oscom/erpexport.php

   1 <?
   2 /***************************************************************
   3 * $Id: erpexport.php,v 1.1 2006/02/06 08:50:30 hli Exp $
   4 *Author: Holger Lindemann
   5 *Copyright: (c) 2004 Lx-System
   6 *License: non free
   7 *eMail: info@lx-system.de
   8 *Version: 1.0.1
   9 *ERP: kivitendo ERP 3.0.0
  10 ***************************************************************/
  11 ?>
  12 <html>
  13         <head><title>Lx-ERP Export der Shopartikel</title>
  14         <link type="text/css" REL="stylesheet" HREF="css/main.css"></link>
  15         <script language="JavaScript">
  16         <!--
  17                 function oscomm() {
  18                         document.fld.PN.value="products_model"; document.fld.partnumber.checked=true;
  19                         document.fld.BEZ.value="products_name"; document.fld.desctiption.checked=true;
  20                         document.fld.GEWICHT.value="products_weight"; document.fld.weight.checked=true;
  21                         document.fld.MWST.value="products_tax"; document.fld.rate.checked=true;
  22                         document.fld.VK.value="products_price"; document.fld.sellprice.checked=true;
  23                         document.fld.PG.value="categories_name"; document.fld.partsgroup.checked=true;
  24                         document.fld.BESCHR.value="products_description"; document.fld.notes.checked=true;
  25                         document.fld.LAGER.value="products_quantity"; document.fld.onhand.checked=true;
  26                         document.fld.encl.value="";
  27                         document.fld.deli.value=";";
  28                         document.fld.crln.value="\\n";
  29                         document.fld.head.checked=true;
  30                         document.fld.shop.value="oscomm";
  31                 }
  32         //-->
  33         </script>
  34 <body>
  35
  36 <?php
  37 $login=$_GET["login"];
  38 require_once "DB.php";
  39 require_once "conf$login.php";
  40 $db=DB::connect($ERPdns);
  41 if (!$db) dbFehler("",$db->getDebugInfo());
  42 if (DB::isError($db)) {
  43         dbFehler("",$db->getDebugInfo());
  44         die ($db->getDebugInfo());
  45 };
  46 /****************************************************
  47 * dbFehler
  48 * in: sql,err = string
  49 * out:
  50 * Fehlermeldungen ausgeben
  51 *****************************************************/
  52 function dbFehler($sql,$err) {
  53 global $showErr;
  54         if ($showErr)
  55                 echo "</td></tr></table><font color='red'>$sql : $err</font><br>";
  56 }
  57
  58 function shopartikel_() {
  59 global $db;
  60         $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 ";
  61         $sql.="parts P left join partsgroup PG on  PG.id=P.partsgroup_id ";
  62         $sql.="left join chart c on c.id = P.income_accno_id ";
  63         $sql.="left join tax T on T.taxkey=C.taxkey_id ";
  64         $sql.="where shop = 't'";
  65         $rs=$db->getAll($sql,DB_FETCHMODE_ASSOC);
  66         return $rs;
  67 }
  68 function shopartikel() {
  69 global $db,$pricegroup;
  70         if ($pricegroup>0) {
  71                 $sql="SELECT P.partnumber,P.description,G.price as sellprice,P.sellprice as stdprice, ";
  72                 $sql.="PG.partsgroup,P.notes,P.image,P.onhand,G.pricegroup_id,P.buchungsgruppen_id as bugru FROM ";
  73                 $sql.="parts P left join partsgroup PG on ";
  74                 $sql.="PG.id=P.partsgroup_id left join prices G on G.parts_id=P.id ";
  75                 $sql.="where P.shop='t' and ";
  76                 $sql.="(G.pricegroup_id=$pricegroup or G.pricegroup_id is null) ";
  77                 $sql.="order by P.partnumber";
  78         } else {
  79                 $sql="SELECT P.partnumber,P.description,P.weight,P.sellprice,PG.partsgroup,";
  80                 $sql.="P.notes,P.image,P.onhand,P.buchungsgruppen_id as bugru ";
  81                 $sql.="FROM parts P left join partsgroup PG on PG.id=P.partsgroup_id ";
  82                 $sql.="left join buchungsgruppen B on P.buchungsgruppen_id = B.id ";
  83                 $sql.="WHERE P.shop='t'";
  84         }
  85         $rs=$db->getAll($sql,DB_FETCHMODE_ASSOC);
  86         return $rs;
  87 }
  88 if ($_POST["export"]) {
  89         $data=shopartikel();
  90         $delim=($_POST["deli"])?$_POST["deli"]:",";
  91         if (get_magic_quotes_gpc()) {
  92                 $crln = stripslashes($_POST["crln"]);
  93         }
  94         //Steuertabelle ERP
  95         $sql ="select  BG.id as bugru,T.rate,TK.startdate from buchungsgruppen BG left join chart C ";
  96         $sql.="on BG.income_accno_id_0=C.id left join taxkeys TK on TK.chart_id=C.id left join tax T ";
  97         $sql.="on T.id=TK.tax_id where TK.startdate <= now()";
  98         $rs=$db->getAll($sql,DB_FETCHMODE_ASSOC);
  99         $erptax=array();
 100         foreach ($rs as $row) {
 101         if ($erptax[$row["bugru"]]["startdate"]<$row["startdate"]) {
 102                 $erptax[$row["bugru"]]["startdate"]=$row["startdate"];
 103                 $erptax[$row["bugru"]]["rate"]=$row["rate"]*100;
 104         }
 105         }
 106         $crln = str_replace('\\r', "\015", $crln);
 107         $crln = str_replace('\\n', "\012", $crln);
 108         $crln = str_replace('\\t', "\011", $crln);
 109         $encl=$_POST["encl"];
 110         $i=0;
 111         $f=fopen($ERPdir,"w");
 112         if ($_POST["partnumber"])       {$header.=$_POST["PN"].$delim; };
 113         if ($_POST["desctiption"])      {$header.=$_POST["BEZ"].$delim; };
 114         if ($_POST["unit"])                     {$header.=$_POST["EINHEIT"].$delim; };
 115         if ($_POST["weight"])           {$header.=$_POST["GEWICHT"].$delim; };
 116         if ($_POST["onhand"])           {$header.=$_POST["LAGER"].$delim; };
 117         if ($_POST["rate"])                     {$header.=$_POST["MWST"].$delim; };
 118         if ($_POST["sellprice"])        {$header.=$_POST["VK"].$delim; };
 119         if ($_POST["listprice"])        {$header.=$_POST["EK"].$delim; };
 120         if ($_POST["priceupdate"])      {$header.=$_POST["PDATE"].$delim; };
 121         if ($_POST["partsgroup"])       {$header.=$_POST["PG"].$delim; };
 122         if ($_POST["notes"])            {$header.=$_POST["BESCHR"].$delim; };
 123         if ($_POST["image"])            {$header.=$_POST["IMAGE"].$delim; };
 124         $header=substr($header,0,-1);
 125 ?>
 126 <table class="liste">
 127 <!-- BEGIN Artikel -->
 128 <?      $i=0;
 129         $f=fopen($ERPdir,"w");
 130         if ($_POST["head"]) fputs($f,$header.$crln);
 131         foreach($data as $zeile) {
 132                 $file=""; $html="";
 133                 $PG=$zeile["partsgroup"];
 134                 $mwst=sprintf("%01.4f",$erptax[$zeile["bugru"]]["rate"]);
 135                 $LineCol = $bgcol[$i%2+1];
 136                 if ($_POST["partnumber"])       {$file.=$encl.$zeile["partnumber"].$encl.$delim; $html.="<td>".$zeile["partnumber"]."</td>";};
 137                 if ($_POST["desctiption"])      {$file.=$encl.strtr($zeile["description"],chr(13).chr(10),"  ").$encl.$delim; $html.="<td>".$zeile["description"]."</td>";};
 138                 if ($_POST["unit"])                     {$file.=$encl.$zeile["unit"].$encl.$delim; $html.="<td>".$zeile["unit"]."</td>";};
 139                 if ($_POST["weight"])           {$file.=$encl.$zeile["weight"].$encl.$delim; $html.="<td>".$zeile["weight"]."</td>";};
 140                 if ($_POST["onhand"])           {$file.=$encl.$zeile["onhand"].$encl.$delim; $html.="<td>".$zeile["onhand"]."</td>";};
 141                 if ($_POST["rate"])                     {$file.=$encl.$mwst.$encl.$delim; $html.="<td>".$mwst."</td>";};
 142                 if ($_POST["sellprice"])        {$file.=$encl.(sprintf("%02.2f",$zeile["sellprice"])).$encl.$delim; $html.="<td>".(sprintf("%02.2f",$zeile["sellprice"]))."</td>";};
 143                 if ($_POST["listprice"])        {$file.=$encl.(sprintf("%02.2f",$zeile["listprice"])).$encl.$delim; $html.="<td>".(sprintf("%02.2f",$zeile["listprice"]))."</td>";};
 144                 if ($_POST["priceupdate"])      {$file.=$encl.$zeile["priceupdate"].$encl.$delim; $html.="<td>".$zeile["priceupdate"]."</td>";};
 145                 if ($_POST["partsgroup"])       {$file.=$encl.$PG.$encl.$delim; $html.="<td>".$zeile["partsgroup"]."</td>";};
 146                 if ($_POST["notes"])            {$file.=$encl.strtr($zeile["notes"],chr(13).chr(10),"  ").$encl.$delim; $html.="<td>".$zeile["notes"]."</td>";};
 147                 if ($_POST["image"])            {$file.=$encl.$zeile["image"].$encl.$delim; $html.="<td>".$zeile["image"]."</td>";};
 148                 $i++;
 149                 fputs($f,substr($file,0,-1).$crln);
 150                 if ($_POST["show"]) {
 151 ?>
 152         <tr  class="smal" onMouseover="this.bgColor='#FF0000';" onMouseout="this.bgColor='<?= $LineCol ?>';" bgcolor="<?= $LineCol ?>">
 153                 <?= $html ?>
 154         </tr>
 155 <?              }
 156         }
 157 ?>
 158 <!-- END Artikel -->
 159 </table>
 160 Anzahl der Artikel: <?= $i ?><br>
 161 Export am : <?= date("d.m.Y : H:i") ?><br>
 162 download <a href="tmp/shopartikel.csv">Exportfile</a><br><hr>
 163 <?
 164         fclose($f);
 165 } // if ($export)
 166 ?>
 167 Export der Shopartikel aus Lx-ERP <br>
 168 M&ouml;gliche Felder
 169 <form name="fld" action="erpexport.php" method="post">
 170 <input type="hidden" name="shop" value="">
 171 <table>
 172         <tr>
 173                 <td><input type="checkbox" name="partnumber" value="1">Artikelnummer</td>
 174                 <td><input type="checkbox" name="desctiption" value="1">Bezeichnung</td>
 175                 <td><input type="checkbox" name="unit" value="1">Einheit</td>
 176                 <td><input type="checkbox" name="weight" value="1">Gewicht</td>
 177         </tr>
 178         <tr>
 179                 <td><input type="text" name="PN" size="23"></td>
 180                 <td><input type="text" name="BEZ" size="23"></td>
 181                 <td><input type="text" name="EINHEIT" size="23"></td>
 182                 <td><input type="text" name="GEWICHT" size="23"></td>
 183         </tr>
 184         <tr><td colspan=5></td></tr>
 185         <tr>
 186                 <td><input type="checkbox" name="sellprice" value="1">Verkaufspreis</td>
 187                 <td><input type="checkbox" name="listprice" value="1">Listenpreis</td>
 188                 <td><input type="checkbox" name="onhand" value="1">Lagerbestand</td>
 189                 <td><input type="checkbox" name="rate" value="1">MwSt</td>
 190         </tr>
 191         <tr>
 192                 <td><input type="text" name="VK" size="23"></td>
 193                 <td><input type="text" name="EK" size="23"></td>
 194                 <td><input type="text" name="LAGER" size="23"></td>
 195                 <td><input type="text" name="MWST" size="23"></td>
 196         </tr>
 197         <tr><td colspan=5></td></tr>
 198         <tr>
 199                 <td><input type="checkbox" name="partsgroup" value="1">Gruppe</td>
 200                 <td><input type="checkbox" name="notes" value="1">Beschreibung</td>
 201                 <td><input type="checkbox" name="image" value="1">Bild</td>
 202                 <td><input type="checkbox" name="show" value="1" checked>HTML-Anzeige</td>
 203         </tr>
 204         <tr>
 205                 <td><input type="text" name="PG" size="23"></td>
 206                 <td><input type="text" name="BESCHR" size="23"></td>
 207                 <td><input type="text" name="IMAGE" size="23"></td>
 208                 <td></td>
 209         </tr>
 210         <tr>
 211                 <td>Feldtrenner <input type="text" name="deli" size="2" value=","></td>
 212                 <td>Feldumrahmung <input type="text" name="encl" size="2" value="&quot;"></td>
 213                 <td>Zeilenende <input type="text" name="crln" size="2" value="\n"></td>
 214                 <td><input type="checkbox" name="head" value="1" checked>Headline</td>
 215         </tr>
 216         <tr>
 217                 <td colspan=5><input type="submit" name="export" value="Export"> <input type="button" name="osc" value="osCommerce" onClick="oscomm()"></td>
 218         </tr>
 219 </table>
 220 <a href="trans.php">zur&uuml;ck</a>
 221 </form>
 222 </body>
 223 </html>