4 shop muß gesetzt sein, obsolet nicht
6 Multishop: Hierfür müssen benutzerdefinierte Variablen angelegt werden.
7 Typ:checkbox, Name=shop[0-9A-Z]+, Bearbeitbar=nein
21 var $docustnr = false;
25 var $OEinsPart = false;
26 var $INVnetto = true; //Rechnungen mit Nettopreisen
27 var $SHOPincl = true; //Shoppreise sind Brutto
29 function erp($db,$error,$divStd,$divVerm,$doordnr,$docustnr,$preordnr,$precustnr,$INVnetto,$SHOPincl,$OEinsPart,$lager) {
31 $this->error = $error;
32 $this->divStd = $divStd;
33 $this->divVerm = $divVerm;
34 $this->doordnr = $doordnr;
35 $this->preordnr = $preordnr;
36 $this->docustnr = $docustnr;
37 $this->precustnr = $precustnr;
38 $this->INVnetto = ($INVnetto == 1)?true:false;
39 $this->SHOPincl = ($SHOPincl == 1)?true:false;
40 $this->OEinsPart = ($OEinsPart == 1)?true:false;
41 $this->lager = ($lager)?$lager:1;
46 $sql = "SELECT BG.id AS bugru,T.rate,TK.startdate,C.taxkey_id, ";
47 $sql .= "(SELECT id FROM chart WHERE accno = T.taxnumber) AS tax_id, ";
48 $sql .= "BG.income_accno_id_0,BG.expense_accno_id_0 ";
49 $sql .= "FROM buchungsgruppen BG LEFT JOIN chart C ON BG.income_accno_id_0=C.id ";
50 $sql .= "LEFT JOIN taxkeys TK ON TK.chart_id=C.id ";
51 $sql .= "LEFT JOIN tax T ON T.id=TK.tax_id WHERE TK.startdate <= now()";
52 $rs = $this->db->getAll($sql);
53 if ($rs) foreach ($rs as $row) {
55 if (!$this->TAX[$nr]) {
57 $data['startdate'] = $row['startdate'];
58 $data['rate'] = $row['rate'];
59 $data['taxkey'] = $row['taxkey_id'];
60 $data['taxid'] = $row['tax_id'];
61 $data['income'] = $row['income_accno_id_0'];
62 $data['expense'] = $row['expense_accno_id_0'];
63 $this->TAX[$nr] = $data;
64 } else if ($this->TAX[$nr]['startdate'] < $row['startdate']) {
65 $this->TAX[$nr]["startdate"] = $row['startdate'];
66 $this->TAX[$nr]["rate"] = $row['rate'];
67 $this->TAX[$nr]["taxkey"] = $row['taxkey_id'];
68 $this->TAX[$nr]["taxid"] = $row['tax_id'];
69 $this->TAX[$nr]["income"] = $row['income_accno_id_0'];
70 $this->TAX[$nr]["expense"] = $row['expense_accno_id_0'];
75 function getParts($stdprice=0,$shop=0) {
76 $where = "WHERE 1=1 ";
78 $sql = "SELECT P.partnumber,P.description,P.notes,P.weight,G.price as sellprice,P.sellprice as stdprice,";
79 $sql .= "PG.partsgroup,P.image,P.buchungsgruppen_id as bugru,P.unit";
81 $sql .= ",(select sum(qty) from inventory where bin_id = ".$this->lager." and parts_id = P.id) as onhand ";
85 $sql .= "FROM parts P ";
86 $sql .= "LEFT JOIN partsgroup PG on PG.id=P.partsgroup_id ";
87 $sql .= "LEFT JOIN prices G on G.parts_id=P.id ";
88 $where .= "AND (G.pricegroup_id=$stdprice ";
89 $where .= "or G.pricegroup_id is null) ";
91 $sql = "SELECT P.partnumber,P.description,P.notes,P.weight,P.sellprice,";
92 $sql .= "PG.partsgroup,P.image,P.buchungsgruppen_id as bugru,P.unit ";
94 $sql .= ",(select sum(qty) from inventory where bin_id = ".$this->lager." and parts_id = P.id) as onhand ";
98 $sql .= "FROM parts P left join partsgroup PG on PG.id=P.partsgroup_id ";
101 $sql .= "LEFT JOIN custom_variables CV on CV.trans_id=P.id ";
102 $where .= "AND (CV.config_id = $shop AND bool_value = 't')";
104 $where .= "AND shop = 't' ";
105 $where .= "AND obsolete = 'f' ORDER BY P.partnumber";
106 $rs = $this->db->getAll($sql.$where);
107 if ($rs) for($i = 0; $i < count($rs); $i++) {
108 $rs[$i]['tax'] = $this->TAX[$rs[$i]['bugru']]['rate'];
113 function getPartsLang($lang,$alle) {
114 $sql = "SELECT P.partnumber,L.translation,P.description,L.longdescription,P.notes,PG.partsgroup ";
115 $sql .= "FROM parts P left join translation L on L.parts_id=P.id left join partsgroup PG on PG.id=P.partsgroup_id ";
116 $sql .= "WHERE P.shop='t' and (L.language_id = $lang";
118 $sql .= " or L.language_id is Null)";
122 $rs = $this->getAll($sql);
124 if ($rs) foreach ($rs as $row) {
125 if (!$data[$row["partnumber"]]) $data[$row["partnumber"]]=$row;
129 function getNewNr($typ) {
132 customer = Kundennummer
135 $sql = "SELECT $typ FROM defaults";
136 $rs = $this->db->getOne($sql);
137 $i=strlen($rs["$typ"])-1;
138 //Nummern können Buchstaben, Zeichen und Zahlen enthalten
139 //nur die Zahlen von rechts werden aber inkrementiert.
141 if ($rs["$typ"][$i] >= "0" and $rs["$typ"][$i]<="9") {
142 $n=$rs["$typ"][$i].$n;
145 $pre = substr($rs["$typ"],0,$i+1);
151 $sql = "UPDATE defaults SET $typ = '$sonr'";
152 $rc = $this->db->query($sql);
154 $this->error->write('erplib','Neue Nummer ($typ) nicht gesichert: '.$sonr);
158 function newOrder($data) {
159 /*Einen neuen Auftrag anlegen. Folgendes Array muß übergeben werden:
160 $data = array(ordnumber,customer_id,employee_id,taxzone_id,amount,netamount,transdate,notes,intnotes,shipvia)
163 $incltax = ($this->INVnetto)?'f':'t';
164 $sql = "INSERT INTO oe (ordnumber,customer_id,employee_id,taxzone_id,taxincluded,curr,amount,netamount,transdate,notes,intnotes,shipvia,cusordnumber) ";
165 $sql .= "values (:ordnumber,:customer_id,:employee_id,:taxzone_id,'$incltax',:curr,:amount,:netamount,:transdate,:notes,:intnotes,:shipvia,:cusordnumber)";
166 $rc = $this->db->insert($sql,$data);
167 $sql = "SELECT * FROM oe where ordnumber = '".$data["ordnumber"]."'";
168 $rs = $this->db->getOne($sql);
170 $this->error->write('erplib','Auftrag erzeugen: '.$data["ordnumber"]);
171 $this->db->rollback();
174 $this->err->out(" Auftrag: ".$data["ordnumber"]." ");
178 function insParts($trans_id,$data,$longtxt) {
179 /*Artikel in die orderitem einfügen. Folgende Daten müssen übergeben werden:
180 $trans_id = (int) oe.id
181 $data = array(trans_id,partnumber,description,longdescription,qty,sellprice,unit)*/
182 foreach ($data as $row) {
183 $row['trans_id'] = $trans_id;
184 //$sql = "SELECT id FROM parts WHERE partnumber = '".$row['partnumber']."'";
185 //$tmp = $this->db->getOne($sql);
186 $tmp = $this->chkPartnumber($row,$this->OEinsPart,true);
188 $row['parts_id'] = $tmp['id'];
190 if ($this->TAX[$this->divStd['BUGRU']]['rate'] == $row['mwst']/100) {
191 $row['parts_id'] = $this->divStd['ID'];
192 } else if ($this->TAX[$this->divVerm['BUGRU']]['rate'] == $row['mwst']/100) {
193 $row['parts_id'] = $this->divVerm['ID'];
195 $row['parts_id'] = $this->divStd['ID'];
198 if ($this->INVnetto) {
200 $row['sellprice'] = round($row['sellprice'] / (100 + $row['taxrate']) * 100,2);
202 if (!$this->SHOPincl)
203 $row['sellprice'] = round($row['sellprice'] * (100 + $row['taxrate']) * 100,2);
205 $row['unit'] = $this->chkUnit($row['unit']);
207 //$row['longdescription'] = addslashes($row['longdescription']);
208 $row['longdescription'] = $row['longdescription'];
210 //$row['longdescription'] = addslashes($tmp['longdescription']);
211 $row['longdescription'] = $tmp['longdescription'];
213 //$row['description'] = addslashes($row['description']);
214 $sql = "INSERT INTO orderitems (trans_id,parts_id,description,longdescription,qty,sellprice,unit,pricegroup_id,discount) ";
215 $sql .= "VALUES (:trans_id,:parts_id,:description,:longdescription,:qty,:sellprice,:unit,0,0)";
216 $row["trans_id"]=$trans_id;
217 $rc = $this->db->insert($sql,$row);
219 $this->db->rollback();
226 function insCustomer($data) {
227 $this->err->out('Insert:'.$data["name"].' ');
228 if ($this->docustnr == 1) {
229 $data['customernumber'] = $this->getNewNr('customer');
231 $data['customernumber'] = $data['shopid'];
233 $data['customernumber'] = $this->precustnr.$data['customernumber'];
234 if ($data['customernumber']>0) {
235 if (!$data['greeting']) $data['greeting'] = '';
236 $sql = "INSERT INTO customer (greeting,name,street,city,zipcode,country,contact,phone,email,customernumber)";
237 $sql .= " VALUES (:greeting,:name,:street,:city,:zipcode,:country,:contact,:phone,:email,:customernumber)";
238 $rc = $this->db->insert($sql,$data);
239 $sql = "SELECT id FROM customer WHERE customernumber = '".$data['customernumber']."'";
240 $rs = $this->db->getOne($sql);
242 $this->err->out("Kd-Nr: ".$data['customernumber'].":".$rs['id']);
244 $this->error->write('erplib','Kunde anlegen: '.$data["name"]);
245 $this->db->rollback();
250 function chkCustomer($data) {
251 if ($data['customer_id']>0) {
252 $sql = "SELECT * FROM customer WHERE id = ".$data['customer_id'];
253 $rs = $this->db->getOne($sql);
254 if ($rs['id'] == $data['customer_id']) {
255 $this->err->out('Update:'.$data['customer_id'].' ');
256 $sql = "UPDATE customer SET greeting = :greeting,name = :name,street = :street,city = :city,country = :country,";
257 $sql .= "zipcode = :zipcode,contact = :contact,phone = :phone,email = :email WHERE id = :customer_id";
258 $rc = $this->db->update($sql,$data);
259 if ($rc) $rc = $data['customer_id'];
261 $rc = $this->insCustomer($data);
264 $rc = $this->insCustomer($data);
268 function mkAuftrag($data,$shop,$longtxt) {
270 $data["notes"] .= "\nBezahlung:".$data['bezahlung']."\n";
271 if ($data['bezahlung'] == "Kreditkarte") $data["notes"] .= $data['kreditkarte']."\n";
273 $data["intnotes"] = "Shop: $shop";
275 $data["intnotes"] = "";
277 $data["customer_id"] = $this->chkCustomer($data["customer"]);
278 $parts = $data['parts'];
279 unset($data['parts']);
280 unset($data['customer']);
281 if ($this->doordnr == 1) {
282 $data["ordnumber"] = $this->getNewNr('so');
284 $data["ordnumber"] = $data['cusordnumber'];
286 $data["ordnumber"] = $this->preordnr.$data["ordnumber"];
287 $tid = $this->newOrder($data);
289 $rc = $this->insParts($tid,$parts,$longtxt);
291 $this->error->write('erplib','Artikel zu Auftrag');
295 $this->error->write('erplib','Auftrag anlegen');
298 $this->err->out($data["customer"]["firma"]." ");
299 $rc = $this->db->Commit();
300 return $data["customer_id"];
302 function chkPartsgroup($pg,$new=True) {
303 /*gibt es die Warengruppe?
304 Rückgabe nichts oder die partsgroup.id
306 $sql = "SELECT * FROM partsgroup WHERE partsgroup = '".$pg."'";
307 $rs = $this->db->getOne($sql);
310 } else if ($this->mkPart and $new) {
311 return $this->mkNewPartsgroup($pg);
316 function mkNewPartsgroup($name) {
317 $sql = "INSERT INTO partsgroup (partsgroup) VALUES ('".$name."')";
318 $rc = $this->db->query($sql);
320 return $this->chkPartsgroup($name,False);
325 function chkUnit($unit) {
326 /*Prüfen ob es die Unit gibt.
327 wenn nicht, die Standardunit zurückgeben*/
329 return $this->stdUnit();
331 $sql = "SELECT * FROM units WHERE name ilike '".$unit."'";
332 $rs = $this->db->getOne($sql);
336 return $this->stdUnit();
341 $sql = "SELECT * FROM units WHERE type = 'dimension' ORDER BY sortkey LIMIT 1";
342 $rs = $this->db->getOne($sql);
345 function chkPartnumber($data,$new=True,$long=false) {
346 $sql = "SELECT * FROM parts WHERE partnumber = '".$data["partnumber"]."'";
347 $rs = $this->db->getOne($sql);
354 } else if ($this->mkPart and $new) {
355 $data['id'] = $this->mkNewPart($data);
365 function mkNewPart($data) {
366 /*eine neue Ware anlegen, sollte nicht direkt aufgerufen werden.
367 Auf vorhandene partnumber wird nicht geprüft.
368 Folgendes Array muß übergeben werden:
369 $data = array(partnumber,description,longdescription,weight,sellprice,taxrate,partsgroup,unit)
372 if ($data['partnumber'] == '') {
373 $this->error->write('erplib','Artikelnummer fehlt');
376 if ($data['description'] == '') {
377 $this->error->write('erplib','Artikelbezeichnung fehlt');
380 $data['notes'] = addslashes($data['longdescription']);
381 if ($data['weight']*1 != $data['weight']) $data['weight']=0;
382 if ($data['sellprice']*1 != $data['sellprice']) $data['sellprice']=0;
383 if (!in_array($data["buchungsgruppen_id"],$this->TAX)) {
384 foreach ($this->TAX as $key=>$tax) {
385 if ($tax["rate"] == $data["taxrate"]/100) {
386 $data["buchungsgruppen_id"] = $key;
390 if (!$data["buchungsgruppen_id"]) {
391 $this->error->write('erplib','Buchungsgruppe konnte nicht zugeordnet werden');
395 if ($data["partsgroup"]) {
396 $data["partsgroup_id"] = $this->chkPartsgroup($data["partsgroup"]);
398 $data["partsgroup_id"] = '';
400 $data['unit'] = $this->chkUnit($data['unit']);
401 if ($data['unit'] == '') {
402 $this->error->write('erplib','Artikeleinheit fehlt oder falsch');
406 $sql = "INSERT INTO parts (partnumber,description,sellprice,weight,notes,shop,unit,partsgroup_id,";
407 $sql .= "image,buchungsgruppen_id,inventory_accno_id,income_accno_id,expense_accno_id) ";
408 $sql .= "VALUES (:partnumber,:description,:sellprice,:weight,:notes,:shop,:unit,:partsgroup_id,";
409 $sql .= ":image,:buchungsgruppen_id,1,1,1)";
410 $rc = $this->db->insert($sql,$data);
411 $x = $this->chkPartnumber($data,False);
412 $this->err->out('Neuer Artikel: '.$data['partnumber'],true);
413 $this->error->write('erplib','Artikel neu: '.$data['partnumber']);