-<?
+<?php
//Henry Margies <h.margies@maxina.de>
//Holger Lindemann <hli@lx-system.de>
}
return $rs[0]["id"];
}
-function insertParts($db,$insert,$show,$data) {
+function getPricegroup($db) {
+ $sql="SELECT * from pricegroup";
+ $rs=$db->getAll($sql);
+ $data = false;
+ if ($rs) foreach ($rs as $row) {
+ $data["pg_".strtolower($row["pricegroup"])]=$row["id"];
+ };
+ return $data;
+}
+function insertParts($db,$insert,$show,$data,$pricegroup) {
if ($show) {
show('<tr>',false);
- show($data["partnumber"]); show($data["lastcost"]); show($data["sellprice"]);
+ show($data["partnumber"]); show($data["lastcost"]); show($data["sellprice"]); show($data["listprice"]);
show($data["description"]); show(substr($data["notes"],0,25));show($data["ean"]);
show($data["weight"]); show($data["image"]); show($data["partsgroup_id"]);
show($data["buchungsgruppen_id"]);show($data["income_accno"]); show($data["expense_accno"]);
show($data["rop"]); show($data["assembly"]); show($data["makemodel"]);
show($data["shop"]);
}
+
/*foreach ($data as $key=>$val) {
echo $key.":".gettype($val).":".gettype($data[$key]).":".$val."<br>";
}*/
if ($insert) {
+ $data["import"]=time();
$sqlIa = 'INSERT INTO parts (';
$sqlIa .= 'partnumber,description,notes,ean,unit,';
- $sqlIa .= 'weight,image,sellprice,lastcost,partsgroup_id,';
+ $sqlIa .= 'weight,image,sellprice,listprice,lastcost,partsgroup_id,';
$sqlIa .= 'buchungsgruppen_id,income_accno_id,expense_accno_id,inventory_accno_id,';
$sqlIa .= 'microfiche,drawing,rop,assembly,shop,makemodel,import) ';
- $sqlIa .= 'VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
- $data["import"]=time();
- $rc=$db->execute($sqlIa,$data);
+ //$sqlIa .= 'VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
+ //$rc=$db->execute($sqlIa,$data);
+ $sqlIa .= "VALUES ('%s','%s','%s','%s','%s',%0.5f,'%s',%0.5f,%0.5f,%0.5f,%d,%d,%d,%d,%d,'%s','%s',%.0f,'%s','%s','%s',%s)";
+ $sql = sprintf($sqlIa,$data['partnumber'],$data['description'],$data['notes'],$data['ean'],
+ $data['unit'],$data['weight'],$data['image'],$data['sellprice'],
+ $data['listprice'],$data['lastcost'],$data['partsgroup_id'],
+ $data['buchungsgruppen_id'],$data['income_accno_id'],$data['expense_accno_id'],
+ $data['inventory_accno_id'],$data['microfiche'],$data['drawing'],$data['rop'],
+ $data['assembly'],$data['shop'],$data['makemodel'],$data['import']);
+ $rc = $db->query($sql);
} else {
$rc = true;
}
+ if ($pricegroup) $ok = insPrices($db,$data["partnumber"],$pricegroup);
if ($show) {
if ($rc)
show('<b>ok</b>');
}
return $rc;
}
-function updPrice($db,$insert,$show,$partnumber,$lastcost,$sellprice,$shop) {
+function updPrice($db,$insert,$show,$partnumber,$lastcost,$sellprice,$listprice,$pricegroup,$shop) {
if ($show) {
show('<tr>',false);
- show($partnumber); show($lastcost); show($sellprice);
+ show($partnumber); show($lastcost); show($sellprice); show($listprice);
}
if ($insert) {
$sqlPr = 'UPDATE PARTS SET ';
- $sqlPr .= 'sellprice = ?, lastcost = ?, shop = ? ';
+ $sqlPr .= 'sellprice = ?, listprice = ?, lastcost = ?, shop = ? ';
$sqlPr .= 'WHERE partnumber = ?';
- $rc=$db->execute($sqlPr,array("sellprice"=>$sellprice,"lastcost"=>$lastcost,"shop"=>$shop,"partnumber"=>$partnumber));
+ $rc=$db->execute($sqlPr,array("sellprice"=>$sellprice,"listprice"=>$listprice,"lastcost"=>$lastcost,"shop"=>$shop,"partnumber"=>$partnumber));
} else {
$rc = true;
}
+ if ($pricegroup) $ok = insPrices($db,$partnumber,$pricegroup);
if ($show) {
if ($rc)
show('<b>ok</b>');
}
return $rc;
}
-function updParts($db,$insert,$show,$partnumber,$lastcost,$sellprice,
+function updParts($db,$insert,$show,$partnumber,$lastcost,$sellprice,$listprice,
$description,$notes,$ean,$weight,$image,
- $partsgroup_id, $shop) {
+ $partsgroup_id,$pricegroup, $shop) {
if ($show) {
show('<tr>',false);
- show($partnumber); show($lastcost); show($sellprice);
+ show($partnumber); show($lastcost); show($sellprice); show($listprice);
show($description); show(substr($notes,0,25));show($ean);
show($weight); show($image); show($partsgroup_id);
}
if ($insert) {
$sqlUa = 'UPDATE PARTS SET ';
$sqlUa .= 'description = ?, notes = ?, ean = ?, weight = ?, image = ?, ';
- $sqlUa .= 'sellprice = ?, lastcost = ?, partsgroup_id = ?, shop = ? ';
+ $sqlUa .= 'sellprice = ?, listprice = ?, lastcost = ?, partsgroup_id = ?, shop = ? ';
$sqlUa .= 'WHERE partnumber = ?';
$rc=$db->execute($sqlUa,array($description,$notes,$ean,$weight,$image,
- $sellprice,$lastcost,$partsgroup_id,$shop,$partnumber));
+ $sellprice,$listprice,$lastcost,$partsgroup_id,$shop,$partnumber));
} else {
$rc = true;
}
+ if ($pricegroup) $ok = insPrices($db,$partnumber,$pricegroup);
if ($show) {
if ($rc)
show('<b>ok</b>');
return $rc;
}
-function getMakemodel($db,$check,$hersteller,$model,$partsid,$add=true) {
+function getMakemodel($db,$check,$hersteller,$model,$partsid,$lastcost,$add=true) {
$sql="select * from makemodel where make = $hersteller and model = '$model' and parts_id = $partsid";
$rs=$db->getAll($sql);
if (empty($rs[0]["id"]) && $add) {
- $sql="insert into makemodel (parts_id,make,model) values ($partsid,'$hersteller','$model')";
+ if (!$lastcost) $lastcost=0.00;
+ $sql="insert into makemodel (parts_id,make,model,lastcost,lastupdate,sortorder) ";
+ $sql.="values ($partsid,'$hersteller','$model',$lastcost,now(),1)";
$rc=$db->query($sql);
}
}
$rs=$db->getAll($sql);
if ($rs[0]["articlenumber"]) {
preg_match("/([^0-9]+)?([0-9]+)([^0-9]+)?/", $rs[0]["articlenumber"] , $regs);
- print_r($regs);
$number=$regs[1].($regs[2]+1).$regs[3];
}
$sql = "update defaults set articlenumber = '$number'";
return $rs[0]["name"];
}
-function import_parts($db, $file, $trenner, $trennzeichen, $fields, $check, $insert, $show ,$maske) {
+function insPrices($db,$pid,$prices) {
+ $rc = $db->query("BEGIN");
+ $sql="delete from prices where parts_id = (select id from parts where partnumber = '$pid')";
+ $rc = $db->query($sql);
+ $sql = "insert into prices (parts_id,pricegroup_id,price) values ((select id from parts where partnumber = '%s'),%d,%0.5f)";
+ foreach ($prices as $key => $val) {
+ $rc = $db->query(sprintf($sql,$pid,$key,$val));
+ if (!$rc) {
+ $db->query("ROLLBACK");
+ return false;
+ }
+ }
+ $db->query("COMMIT");
+ return true;
+}
+
+/**
+ * TODO: short description.
+ *
+ * @param double $db
+ * @param mixed $file
+ * @param mixed $fields
+ * @param mixed $check
+ * @param int $insert
+ * @param string $show
+ * @param mixed $maske
+ *
+ * @return TODO
+ */
+function import_parts($db, $file, $fields, $check, $maske) {
+ $insert = !$maske["test"];
+ $show = $maske["show"];
+ $trennzeichen = ($maske["trennzeichen"])?$maske["trennzeichen"]:"";
+ $trenner = ($maske["trenner"])?$maske["trenner"]:",";
$precision=$maske["precision"];
$quotation=$maske["quotation"];
$quottype=$maske["quottype"];
$wgtrenner=$maske["wgtrenner"];
$Update=($maske["update"]=="U")?true:false;
$UpdText=($maske["TextUpd"]=="1")?true:false;
+ $vendnr=($maske["vendnr"]=="t")?true:false;
+ $modnr=($maske["modnr"]=="t")?true:false;
- $stdunitW=getStdUnit($db,"dimension");
- $stdunitD=getStdUnit($db,"service");
+ //$stdunitW=getStdUnit($db,"dimension");
+ //$stdunitD=getStdUnit($db,"service");
+ $stdunitW=$maske["dimensionunit"];
+ $stdunitD=$maske["serviceunit"];
if ($quottype=="P") $quotation=($quotation+100)/100;
if ($show && !$insert) show("<b>Testimport</b>",false);
if ($show) show("<table border='1'>\n",false);
/* field description */
- $parts_fld = array_keys($fields);
+ $prices = getPricegroup($db);
+ if ($prices) {
+ $priceskey = array_keys($prices);
+ $parts_fld = array_merge(array_keys($fields),$priceskey);
+ } else {
+ $parts_fld = array_keys($fields);
+ }
+
+ if ($trenner=="other") $trenner=trim($trennzeichen);
+ if (substr($trenner,0,1)=="#") if (strlen($trenner)>1) $trenner=chr(substr($trenner,1));
/* open csv file */
- $f=fopen("$file.csv","r");
-
+ if (file_exists($file."head.csv")) {
+ $fh=fopen($file.'head.csv',"r");
+ // Erst einmal die erste Zeile mit den richtigen Feldbezeichnungen einlesen.
+ $infld=fgetcsv($fh,1200,$trenner);
+ fclose($fh);
+ $f=fopen($file.'.csv',"r");
+ // Erst einmal die erste Zeile mit den falschen Feldbezeichnungen einlesen.
+ $tmp=fgetcsv($f,1200,$trenner);
+ } else {
+ $f=fopen($file.'.csv',"r");
+ // Erst einmal die erste Zeile mit den Feldbezeichnungen einlesen.
+ $infld=fgetcsv($f,1200,$trenner);
+ }
+
/*
* read first line with table descriptions
*/
if ($show) {
show('<tr>',false);
- show("partnumber"); show("lastcost"); show("sellprice");
- show("description");show("notes"); show("ean");
- show("weight"); show("image"); show("partsgroup_id");
- show("bg"); show("income_accno"); show("expense_accno");
- show("inventory_accno"); show("microfiche");show("drawing");show("rop");
- show("assembly");show("makemodel");show("shop"); show("");
+ show("partnumber"); show("lastcost"); show("sellprice"); show("listprice");
+ show("description"); show("notes"); show("ean");
+ show("weight"); show("image"); show("partsgroup_id");
+ show("bg"); show("income_accno"); show("expense_accno");
+ show("inventory_accno"); show("microfiche"); show("drawing");
+ show("rop"); show("assembly"); show("makemodel"); show("shop"); show("");
show("</tr>\n",false);
}
- if ($trenner=="other") $trenner=trim($trennzeichen);
- if (substr($trenner,0,1)=="#") if (strlen($trenner)>1) $trenner=chr(substr($trenner,1));
- // Erst einmal die erste Zeile mit den Feldbezeichnungen einlesen.
- $infld=fgetcsv($f,1200,$trenner);
$p=0;
foreach ($infld as $fld) {
$fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>""))));
while ( ($zeile=fgetcsv($f,120000,$trenner)) != FALSE) {
$m++; /* increase line */
$unit=false;
-
unset($pgroup);
+ unset($partsgroup_id);
unset($notes);
unset($rop);
unset($weight);
/* VK-Preis bilden */
$sellprice = str_replace(",", ".", $zeile[$fldpos["sellprice"]]);
+ $listprice = str_replace(",", ".", $zeile[$fldpos["listprice"]]);
$lastcost = str_replace(",", ".", $zeile[$fldpos["lastcost"]]);
+ if ($prices) {
+ foreach ($prices as $pkey=>$val) {
+ if (array_key_exists($pkey,$fldpos))
+ $pricegroup[$val] = str_replace(",", ".", $zeile[$fldpos[$pkey]]);
+ }
+ }
if ($quotation<>0) {
if ($quottype=="A") { $sellprice += $quotation; }
else { $sellprice = $sellprice * $quotation; }
};
if ($lastcost=="") unset($lastcost);
if ($sellprice=="") unset($sellprice);
+ if ($listprice=="") unset($listprice);
/* Langtext zusammenbauen */
if ($zeile[$fldpos["notes"]]) {
- $notes = preg_replace('/""[^ ]/','"',$zeile[$fldpos["notes"]]);
- $notes = addslashes($notes);
+ //Kundenspezifisch:
+ //$notes = preg_replace('/""[^ ]/','"',$zeile[$fldpos["notes"]]);
+ $notes = addslashes($zeile[$fldpos["notes"]]);
+ if (Translate) translate($notes);
}
if ($zeile[$fldpos["notes1"]]) {
- $notes1 = preg_replace('/""[^ ]/','"',$zeile[$fldpos["notes1"]]);
+ //Kundenspezifisch:
+ //$notes1 = preg_replace('/""[^ ]/','"',$zeile[$fldpos["notes1"]]);
+ $notes1 = addslashes($zeile[$fldpos["notes1"]]);
+ if (Translate) translate($notes1);
if ($notes) {
- $notes = "\n".addslashes($notes1);
+ $notes .= "\n".$notes1;
} else {
- $notes = addslashes($notes1);
+ $notes = $notes1;
}
}
if ($fldpos["partsgroup4"]>0 and $zeile[$fldpos["partsgroup4"]]) $pgroup[]=$zeile[$fldpos["partsgroup4"]];
if (count($pgroup)>0) {
$pgname = implode($wgtrenner,$pgroup);
+ if (Translate) translate($pgname);
$partsgroup_id = getPartsgroupId($db, $pgname, $insert);
}
+ /* Ware oder Dienstleistung */
+ if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="D") or $maske["ware"]=="D") {
+ $artikel = false;
+ } else if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="W") or $maske["ware"]=="W") {
+ $artikel = true;
+ }
+
/* sind Hersteller und Modelnummer hinterlegt
wenn ja, erfolgt er insert später */
- if (!empty($zeile[$fldpos["makemodel"]]) and !$artikel) {
- $hersteller=suchFirma("vendor",$zeile[$fldpos["makemodel"]]);
- $hersteller=$hersteller["cp_cv_id"];
- if (!empty($zeile[$fldpos["model"]])) {
- $model = $zeile[$fldpos["model"]];
+ $makemodel = 'f';
+ if (!empty($zeile[$fldpos["makemodel"]]) and $artikel) {
+ $mm = $zeile[$fldpos["makemodel"]];
+ if (Translate) translate($mm);
+ if ($vendnr) {
+ $hersteller=getFirma($mm,"vendor");
+ } else {
+ $hersteller=suchFirma("vendor",$mm);
+ $hersteller=$hersteller["cp_cv_id"];
+ }
+ if (!empty($zeile[$fldpos["model"]]) and $hersteller>0) {
+ $mo = $zeile[$fldpos["model"]];
+ if (Translate) translate($mo);
+ $model = $mo;
+ $makemodel = 't';
+ } else if ($modnr and $hersteller>0) {
+ $model = '';
$makemodel = 't';
- } else {
+ } else {
unset($hersteller);
$makemodel = 'f';
}
}
- /* Ware oder Dienstleistung */
- if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="D") or $maske["ware"]=="D") {
- $artikel = false;
- } else if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="W") or $maske["ware"]=="W") {
- $artikel = true;
- }
-
/* Einheit ermitteln */
if ($zeile[$fldpos["unit"]]=="") {
//Keine Einheit mitgegeben
$description = preg_replace('/""[^ ]/','"',$zeile[$fldpos["description"]]);
$description = addslashes($description);
+ if (Translate) translate($description);
// rop und weight müssen null oder Zahl sein
if ($zeile[$fldpos["rop"]]) $rop = 1 * str_replace(",", ".",$zeile[$fldpos["rop"]]);
// Shop-Artikel
if ($zeile[$fldpos["shop"]]) {
- $shop = ($zeile[$fldpos["shop"]] > 0)?'t':'f';
+ $shop = (strtolower($zeile[$fldpos["shop"]]=='t'))?'t':'f';
} else {
$shop = $maske["shop"];
}
-
// Artikel updaten
+
if (getPartsid($db,trim($zeile[$fldpos["partnumber"]]))) {
/* es gibt die Artikelnummer */
if ($Update) {
/* Updates durchführen */
if ($UpdText=='1') {
- $u += updParts($db,$insert,$show,$zeile[$fldpos["partnumber"]],$lastcost,$sellprice,
+ $u += updParts($db,$insert,$show,$zeile[$fldpos["partnumber"]],$lastcost,$sellprice,$listprice,
$description,$notes,$zeile[$fldpos["ean"]],$weight,
- $zeile[$fldpos["image"]],$partsgroup_id, $shop);
+ $zeile[$fldpos["image"]],$partsgroup_id,$pricegroup, $shop);
} else {
- $u += updPrice($db,$insert,$show,$zeile[$fldpos["partnumber"]],$lastcost,$sellprice,$shop);
+ $u += updPrice($db,$insert,$show,$zeile[$fldpos["partnumber"]],$lastcost,$sellprice,$listprice,$pricegroup,$shop);
}
continue;
// nächste Zeile
"description"=>$description,"notes"=>$notes,
"ean"=>$zeile[$fldpos["ean"]],"unit"=>$unit,
"weight"=>$weight,"image"=>$zeile[$fldpos["image"]],
- "sellprice"=>$sellprice,"lastcost"=>$lastcost,
+ "sellprice"=>$sellprice,
+ "lastcost"=>$lastcost,
+ "listprice"=>$listprice,
"partsgroup_id"=>$partsgroup_id,
- "buchungsgruppen_id"=>$bg,"income_accno"=>$income_accno,
- "expense_accno"=>$expense_accno,"inventory_accno"=>$inventory_accno,
+ "buchungsgruppen_id"=>$bg,"income_accno_id"=>$income_accno,
+ "expense_accno_id"=>$expense_accno,"inventory_accno_id"=>$inventory_accno,
"microfiche"=>$zeile[$fldpos["microfiche"]],"drawing"=>$zeile[$fldpos["drawing"]],
"rop"=>$rop,"assembly"=>$assembly,
- "shop"=>$shop,"makemodel"=>$makemodel)
+ "shop"=>$shop,"makemodel"=>$makemodel),$pricegroup
);
- if ($hersteller>0 && $model) {
+ if ($hersteller>0 ) { // && $model) {
$partsid=getPartsid($db,$zeile[$fldpos["partnumber"]]);
- if ($partsid) {
- getMakemodel($db,$check,$hersteller,$model,$partsid,true);
+ if ($partsid) {
+ getMakemodel($db,$check,$hersteller,$model,$partsid,$lastcost,true);
}
}
unset($zeile);