Revision 64035a1b
Von Holger Lindemann vor fast 14 Jahren hinzugefügt
lxo-import/addressB.php | ||
---|---|---|
69 | 69 |
$trenner=($_POST["trenner"])?$_POST["trenner"]:","; |
70 | 70 |
if ($trenner=="other") { |
71 | 71 |
$trenner=trim($trennzeichen); |
72 |
if (substr($trenner,0,1)=="#") if (strlen($trenner)>1) $trenner=chr(substr($trenner,1)); |
|
73 | 72 |
} |
73 |
if (substr($trenner,0,1)=="#") if (strlen($trenner)>1) $trenner=chr(substr($trenner,1)); |
|
74 | 74 |
|
75 | 75 |
if (!file_exists($dir.$file.".csv")) ende("$file.csv nicht im Ordner oder leer"); |
76 | 76 |
|
77 |
|
|
78 | 77 |
if (!$db->chkcol($file)) ende("Importspalte kann nicht angelegt werden"); |
79 | 78 |
|
80 | 79 |
$employee=chkUsr($_SESSION["employee"]); |
... | ... | |
114 | 113 |
} |
115 | 114 |
} |
116 | 115 |
|
116 |
function chkBusiness($data,$id=true) { |
|
117 |
global $db; |
|
118 |
if ($id) { |
|
119 |
$rs = $db->getAll("select id from business where id =$data"); |
|
120 |
} else { |
|
121 |
$rs = $db->getAll("select id from business where decription ilike '$data'"); |
|
122 |
} |
|
123 |
if ($rs[0]["id"]) { |
|
124 |
return $rs[0]["id"]; |
|
125 |
} else { |
|
126 |
return "null"; |
|
127 |
} |
|
128 |
} |
|
129 |
|
|
130 |
function chkSalesman($data,$id=true) { |
|
131 |
global $db; |
|
132 |
if ($id) { |
|
133 |
$rs = $db->getAll("select id from employee where id =$data"); |
|
134 |
} else { |
|
135 |
$rs = $db->getAll("select id from employee where login ilike '$data'"); |
|
136 |
} |
|
137 |
if ($rs[0]["id"]) { |
|
138 |
return $rs[0]["id"]; |
|
139 |
} else { |
|
140 |
return "null"; |
|
141 |
} |
|
142 |
} |
|
143 |
|
|
117 | 144 |
$f=fopen($dir.$file.".csv","r"); |
118 | 145 |
$zeile=fgets($f,1200); |
119 | 146 |
$infld=explode($trenner,strtolower($zeile)); |
120 | 147 |
$first=true; |
121 | 148 |
$ok=true; |
149 |
$p=0; |
|
122 | 150 |
foreach ($infld as $fld) { |
123 | 151 |
$fld = strtolower(trim(strtr($fld,array("\""=>"","'"=>"")))); |
124 |
if ($fld=="branche" && !$crm) { $in_fld[]=""; continue; }; |
|
125 |
if ($fld=="sw" && !$crm) { $in_fld[]=""; continue; }; |
|
126 |
$in_fld[]=$fld; |
|
152 |
if (in_array($fld,$kunde_fld)) { |
|
153 |
if ($fld=="branche" && !$crm) { continue; }; |
|
154 |
if ($fld=="sw" && !$crm) { continue; }; |
|
155 |
$in_fld[$fld]=$p; |
|
156 |
//$fldpos[$fld]=$p; |
|
157 |
//$in_fld[]=$fld; |
|
158 |
} |
|
159 |
$p++; |
|
127 | 160 |
} |
128 |
|
|
161 |
$infld = array_keys($in_fld); |
|
162 |
$infld[] = "import"; |
|
163 |
$infld = implode(",",$infld); |
|
129 | 164 |
$j=0; |
130 | 165 |
$m=0; |
131 | 166 |
$zeile=fgetcsv($f,1200,$trenner); |
... | ... | |
135 | 170 |
$anrede=""; |
136 | 171 |
$Matchcode=""; |
137 | 172 |
$sql="insert into $file "; |
138 |
$keys="(";
|
|
139 |
$vals=" values (";
|
|
173 |
$keys=array();
|
|
174 |
$vals=array();
|
|
140 | 175 |
$number=false; |
141 |
foreach($zeile as $data) { |
|
142 |
if (!in_array(trim($in_fld[$i]),$kunde_fld)) { |
|
143 |
if ($in_fld[$i]=="anrede") { $anrede=addslashes(trim($data)); } |
|
144 |
$i++; |
|
145 |
continue; |
|
146 |
}; |
|
147 |
$data=trim($data); |
|
176 |
//foreach($zeile as $data) { |
|
177 |
|
|
178 |
foreach($in_fld as $fld => $pos) { |
|
179 |
switch ($fld) { |
|
180 |
case "name" : |
|
181 |
case "department_1" : |
|
182 |
case "department_2" : |
|
183 |
case "matchcode" : |
|
184 |
case "street" : |
|
185 |
case "city" : |
|
186 |
case "notes" : |
|
187 |
case "sw" : |
|
188 |
case "branche" : |
|
189 |
case "country" : |
|
190 |
case "contact" : |
|
191 |
case "homepage" : |
|
192 |
case "email" : |
|
193 |
case "bank" : $data = addslashes(trim($zeile[$pos])); |
|
194 |
if (Translate) translate($data); |
|
195 |
case "ustid" : $data = strtr(trim($zeile[$pos])," ",""); |
|
196 |
case "bank_code" : $data = trim($zeile[$pos]); |
|
197 |
case "account_number": |
|
198 |
case "greeting" : |
|
199 |
case "taxnumber" : |
|
200 |
case "zipcode" : |
|
201 |
case "phone" : |
|
202 |
case "fax" : $data = trim($zeile[$pos]); |
|
203 |
$data = "'$data'"; |
|
204 |
if ($data=="''") { |
|
205 |
$vals[] = "null"; |
|
206 |
} else { |
|
207 |
$vals[] = $data; |
|
208 |
} |
|
209 |
break; |
|
210 |
case "business_id" : $vals[] = chkBusiness(trim($zeile[$pos])); |
|
211 |
break; |
|
212 |
case "salesman_id" : $vals[] = chkSalesman(trim($zeile[$pos])); |
|
213 |
break; |
|
214 |
case "taxincluded" : $data = strtolower(substr($zeile[$pos],0,1)); |
|
215 |
if ($data!="f" && $data!="t") { $vals[] = "'f'"; } |
|
216 |
else { $vals[] = "'".$data."'";} |
|
217 |
break; |
|
218 |
case "taxzone_id" : $data = trim($zeile[$pos])*1; |
|
219 |
if ($data>3 && $data<0) $data = 0; |
|
220 |
$vals[] = $data; |
|
221 |
break; |
|
222 |
case "creditlimit" : |
|
223 |
case "discount" : |
|
224 |
case "terms" : $vals[] = trim($zeile[$pos])*1; |
|
225 |
break; |
|
226 |
case "customernumber": |
|
227 |
case "vendornumber" : $data = trim($zeile[$pos]); |
|
228 |
if (empty($data) or !$data) { |
|
229 |
$vals[] = getKdId(); |
|
230 |
$number = true; |
|
231 |
} else { |
|
232 |
$vals[] = chkKdId($data); |
|
233 |
$number = true; |
|
234 |
} |
|
235 |
break; |
|
236 |
} |
|
237 |
}; |
|
238 |
if (!in_array("taxzone_id",$in_fld)) { |
|
239 |
$in_fld[] = "taxzone_id"; |
|
240 |
$vals[] = 0; |
|
241 |
} |
|
148 | 242 |
// seit 2.6 ist die DB-Kodierung UTF-8 @holger Ansonsten einmal vorher die DB-Encoding auslesen |
149 | 243 |
// Falls die Daten ISO-kodiert kommen entsprechend wandeln |
150 | 244 |
// done! |
... | ... | |
152 | 246 |
// die blöde mb_detect... tut leider nicht immer, daher die Möglichkeit der Auswahl |
153 | 247 |
// TODO Umlaute am Anfang wurden bei meinem Test nicht übernommen (Österreich). S.a.: |
154 | 248 |
// http://forum.de.selfhtml.org/archiv/2007/1/t143904/ |
155 |
|
|
156 |
if (Translate) translate($data); |
|
157 |
|
|
158 |
//$data=htmlentities($data); |
|
159 |
$data=addslashes($data); |
|
160 |
if ($in_fld[$i]==$file."number") { // customernumber || vendornumber |
|
161 |
if (empty($data) or !$data) { |
|
162 |
$data=getKdId(); |
|
163 |
$number=true; |
|
164 |
} else { |
|
165 |
$data=chkKdId($data); |
|
166 |
$number=true; |
|
167 |
} |
|
168 |
} else if ($in_fld[$i]=="taxincluded"){ |
|
169 |
$data=strtolower(substr($data,0,1)); |
|
170 |
if ($data!="f" && $data!="t") $data="f"; |
|
171 |
} else if ($in_fld[$i]=="ustid"){ |
|
172 |
$data=strtr($data," ",""); |
|
173 |
} /*else if ($in_fld[$i]=="matchcode") { |
|
174 |
$matchcode=$data; |
|
175 |
$i++; |
|
176 |
continue; |
|
177 |
if ($data==false or empty($data) or !$data) { |
|
178 |
if (in_array($in_fld[$i],array("name"))) { |
|
179 |
$data=$matchcode; |
|
180 |
} |
|
181 |
} |
|
182 |
}*/ |
|
183 |
|
|
184 |
$keys.=$in_fld[$i].","; |
|
185 |
if ($data==false or empty($data) or !$data) { |
|
186 |
$vals.="null,"; |
|
187 |
} else { |
|
188 |
if ($in_fld[$i]=="contact"){ |
|
189 |
if ($anrede) { |
|
190 |
$vals.="'$anrede $data',"; |
|
191 |
} else { |
|
192 |
$vals.="'$data',"; |
|
193 |
} |
|
194 |
} else { |
|
195 |
$vals.="'".$data."',"; |
|
196 |
} |
|
197 |
} |
|
198 |
$i++; |
|
199 |
} |
|
200 |
if (!$number) { |
|
201 |
$keys.=$file."number,"; |
|
202 |
$vals.="'".getKdId()."',"; |
|
203 |
} |
|
204 |
if ($keys<>"(") { |
|
205 |
if ($test) { |
|
249 |
if ($test) { |
|
206 | 250 |
if ($first) { |
207 |
echo "<table border='1'>\n"; |
|
208 |
echo "<tr><th>".str_replace(",","</th><th>",substr($keys,1,-1))."</th></tr>\n"; |
|
251 |
echo "<table border='1'>\n<tr><td>"; |
|
252 |
echo implode('</th><th>',array_keys($in_fld)); |
|
253 |
echo "</td></tr>\n"; |
|
209 | 254 |
$first=false; |
210 | 255 |
}; |
211 |
$vals=str_replace("',","'</td><td>",substr($vals,9,-1)); |
|
212 |
echo "<tr><td>".str_replace("null,","null</td><td>",$vals)."</td></tr>\n"; |
|
256 |
echo "<tr><td>"; |
|
257 |
echo implode('</td><td>',$vals); |
|
258 |
echo "</td></tr>\n"; |
|
213 | 259 |
//echo "Import $j<br>\n"; |
214 | 260 |
flush(); |
215 |
} else { |
|
216 |
$sql.=$keys."taxzone_id,import)"; |
|
217 |
$sql.=$vals."0,$nun)"; |
|
218 |
$rc=$db->query($sql); |
|
219 |
if (!$rc) echo "Fehler: ".$sql."<br>"; |
|
220 |
} |
|
221 |
$j++; |
|
222 | 261 |
} else { |
223 |
$vals=str_replace("',","'</td><td>",substr($vals,9,-1)); |
|
224 |
echo "<tr><td style=\"color:red\">".str_replace("null,","null</td><td style=\"color:red\">",$vals)."</td></tr>\n"; |
|
225 |
flush(); |
|
226 |
} |
|
262 |
$vals[] = $nun; |
|
263 |
$sql = "INSERT INTO $file (".$infld.") values (".implode(",",$vals).")"; |
|
264 |
$rc=$db->query($sql); |
|
265 |
if ($j % 10 == 0) { echo "."; flush(); }; |
|
266 |
if (!$rc) { echo "<br />Fehler: ".$sql."<br />"; flush(); }; |
|
267 |
} |
|
268 |
$j++; |
|
227 | 269 |
$zeile=fgetcsv($f,1200,$trenner); |
228 | 270 |
} |
229 | 271 |
fclose($f); |
lxo-import/import_lib.php | ||
---|---|---|
36 | 36 |
"bank_code" => "Bankleitzahl", |
37 | 37 |
"bank" => "Bankname", |
38 | 38 |
"branche" => "Branche", |
39 |
"business_id" => "BranchenID", |
|
40 |
"salesman_id" => "Verk?uferID", |
|
39 | 41 |
//"language" => "Sprache (de,en,fr)", |
40 | 42 |
"sw" => "Stichwort", |
41 | 43 |
"creditlimit" => "Kreditlimit (nnnnnn.nn)"); /*, |
lxo-import/partsB.php | ||
---|---|---|
92 | 92 |
echo "Dazu muß entweder in der Maske eine Standardbuchungsgruppe gewählt werden <br>"; |
93 | 93 |
echo "oder es wird ein gültiges Konto in 'income_accno_id' und 'expense_accno_id' eingegeben. "; |
94 | 94 |
echo "Das Programm versucht dann eine passende Buchungsgruppe zu finden.<br>"; |
95 |
echo "Preisgruppen müssen zunächst angelegt werden. Die Spalten für die Preisgruppen beginnen mit 'pg_' gefolgt vom Preisgruppenname.<br>"; |
|
96 |
echo "Wenn eine Datei <code>partshead.csv</code> existiert, wird die erste Zeile aus dieser benutzt um das Format zu bestimmen. Die erste Zeile aus der eigentlichen Importdatei wird dann ignoriert.<br>"; |
|
95 |
echo "Preisgruppen müssen zunächst angelegt werden. Die Spalten für die Preisgruppen beginnen mit 'pg_' gefolgt vom Preisgruppenname.<br>";
|
|
96 |
echo "Wenn eine Datei <code>users/partshead.csv</code> existiert, wird die erste Zeile aus dieser benutzt um das Format zu bestimmen. Die erste Zeile aus der eigentlichen Importdatei wird dann ignoriert.<br>";
|
|
97 | 97 |
exit(0); |
98 | 98 |
}; |
99 | 99 |
|
... | ... | |
203 | 203 |
<tr><td>Textupdate</td><td><input type="checkbox" name="TextUpd" value="1">ja</td></tr> |
204 | 204 |
<tr><td>Warengruppen<br>verbinder</td><td><input type="text" name="wgtrenner" value="!" size="3"></td></tr> |
205 | 205 |
<tr><td>Shopartikel<br />falls nicht übergeben</td><td><input type="radio" name="shop" value="t">ja <input type="radio" name="shop" value="f" checked>nein</td></tr> |
206 |
<tr><td>Art</td><td><input type="Radio" name="ware" value="W" checked>Ware |
|
206 |
<tr><td>Eintrag in<br />Makemodel ist</td><td><input type="radio" name="vendnr" value="t">Lieferantennummer <input type="radio" name="vendnr" value="f" checked>Lieferantenname</td></tr> |
|
207 |
<tr><td>auch ohne<br />Model-Nr.</td><td><input type="radio" name="modnr" value="t">ja <input type="radio" name="modnr" value="f" checked>nein</td></tr> |
|
208 |
<tr><td>Art</td><td><input type="Radio" name="ware" value="W" checked>Ware |
|
207 | 209 |
<input type="Radio" name="ware" value="D">Dienstleistung |
208 | 210 |
<input type="Radio" name="ware" value="G">gemischt (Spalte 'art' vorhanden)</td></tr> |
209 | 211 |
<tr><td>Default Einheiten<br></td><td><select name="dimensionunit"> |
lxo-import/parts_import.php | ||
---|---|---|
128 | 128 |
return $rc; |
129 | 129 |
} |
130 | 130 |
|
131 |
function getMakemodel($db,$check,$hersteller,$model,$partsid,$add=true) { |
|
131 |
function getMakemodel($db,$check,$hersteller,$model,$partsid,$lastcost,$add=true) {
|
|
132 | 132 |
$sql="select * from makemodel where make = $hersteller and model = '$model' and parts_id = $partsid"; |
133 | 133 |
$rs=$db->getAll($sql); |
134 | 134 |
if (empty($rs[0]["id"]) && $add) { |
135 |
$sql="insert into makemodel (parts_id,make,model) values ($partsid,'$hersteller','$model')"; |
|
135 |
if (!$lastcost) $lastcost=0.00; |
|
136 |
$sql="insert into makemodel (parts_id,make,model,lastcost,lastupdate,sortorder) "; |
|
137 |
$sql.="values ($partsid,'$hersteller','$model',$lastcost,now(),1)"; |
|
136 | 138 |
$rc=$db->query($sql); |
137 | 139 |
} |
138 | 140 |
} |
... | ... | |
251 | 253 |
$wgtrenner=$maske["wgtrenner"]; |
252 | 254 |
$Update=($maske["update"]=="U")?true:false; |
253 | 255 |
$UpdText=($maske["TextUpd"]=="1")?true:false; |
256 |
$vendnr=($maske["vendnr"]=="t")?true:false; |
|
257 |
$modnr=($maske["modnr"]=="t")?true:false; |
|
254 | 258 |
|
255 | 259 |
//$stdunitW=getStdUnit($db,"dimension"); |
256 | 260 |
//$stdunitD=getStdUnit($db,"service"); |
... | ... | |
293 | 297 |
*/ |
294 | 298 |
if ($show) { |
295 | 299 |
show('<tr>',false); |
296 |
show("partnumber"); show("lastcost"); show("sellprice"); show("listprice");
|
|
297 |
show("description");show("notes"); show("ean");
|
|
298 |
show("weight"); show("image"); show("partsgroup_id");
|
|
299 |
show("bg"); show("income_accno"); show("expense_accno");
|
|
300 |
show("inventory_accno"); show("microfiche");show("drawing");show("rop");
|
|
301 |
show("assembly");show("makemodel"); show("shop"); show("");
|
|
300 |
show("partnumber"); show("lastcost"); show("sellprice"); show("listprice");
|
|
301 |
show("description"); show("notes"); show("ean");
|
|
302 |
show("weight"); show("image"); show("partsgroup_id");
|
|
303 |
show("bg"); show("income_accno"); show("expense_accno");
|
|
304 |
show("inventory_accno"); show("microfiche"); show("drawing");
|
|
305 |
show("rop"); show("assembly"); show("makemodel"); show("shop"); show("");
|
|
302 | 306 |
show("</tr>\n",false); |
303 | 307 |
} |
304 | 308 |
|
... | ... | |
342 | 346 |
foreach ($prices as $pkey=>$val) { |
343 | 347 |
if (array_key_exists($pkey,$fldpos)) |
344 | 348 |
$pricegroup[$val] = str_replace(",", ".", $zeile[$fldpos[$pkey]]); |
345 |
}
|
|
346 |
}
|
|
349 |
}
|
|
350 |
}
|
|
347 | 351 |
if ($quotation<>0) { |
348 | 352 |
if ($quottype=="A") { $sellprice += $quotation; } |
349 | 353 |
else { $sellprice = $sellprice * $quotation; } |
... | ... | |
383 | 387 |
$partsgroup_id = getPartsgroupId($db, $pgname, $insert); |
384 | 388 |
} |
385 | 389 |
|
390 |
/* Ware oder Dienstleistung */ |
|
391 |
if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="D") or $maske["ware"]=="D") { |
|
392 |
$artikel = false; |
|
393 |
} else if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="W") or $maske["ware"]=="W") { |
|
394 |
$artikel = true; |
|
395 |
} |
|
396 |
|
|
386 | 397 |
/* sind Hersteller und Modelnummer hinterlegt |
387 | 398 |
wenn ja, erfolgt er insert sp?ter */ |
388 | 399 |
$makemodel = 'f'; |
389 |
if (!empty($zeile[$fldpos["makemodel"]]) and !$artikel) {
|
|
400 |
if (!empty($zeile[$fldpos["makemodel"]]) and $artikel) { |
|
390 | 401 |
$mm = $zeile[$fldpos["makemodel"]]; |
391 | 402 |
if (Translate) translate($mm); |
392 |
$hersteller=suchFirma("vendor",$mm); |
|
393 |
$hersteller=$hersteller["cp_cv_id"]; |
|
394 |
if (!empty($zeile[$fldpos["model"]])) { |
|
403 |
if ($vendnr) { |
|
404 |
$hersteller=getFirma($mm,"vendor"); |
|
405 |
} else { |
|
406 |
$hersteller=suchFirma("vendor",$mm); |
|
407 |
$hersteller=$hersteller["cp_cv_id"]; |
|
408 |
} |
|
409 |
if (!empty($zeile[$fldpos["model"]]) and $hersteller>0) { |
|
395 | 410 |
$mo = $zeile[$fldpos["model"]]; |
396 | 411 |
if (Translate) translate($mo); |
397 | 412 |
$model = $mo; |
398 | 413 |
$makemodel = 't'; |
399 |
} else { |
|
414 |
} else if ($modnr and $hersteller>0) { |
|
415 |
$model = ''; |
|
416 |
$makemodel = 't'; |
|
417 |
} else { |
|
400 | 418 |
unset($hersteller); |
401 | 419 |
$makemodel = 'f'; |
402 | 420 |
} |
403 | 421 |
} |
404 | 422 |
|
405 |
/* Ware oder Dienstleistung */ |
|
406 |
if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="D") or $maske["ware"]=="D") { |
|
407 |
$artikel = false; |
|
408 |
} else if (($maske["ware"]=="G" and strtoupper($zeile[$fldpos["art"]])=="W") or $maske["ware"]=="W") { |
|
409 |
$artikel = true; |
|
410 |
} |
|
411 |
|
|
412 | 423 |
/* Einheit ermitteln */ |
413 | 424 |
if ($zeile[$fldpos["unit"]]=="") { |
414 | 425 |
//Keine Einheit mitgegeben |
... | ... | |
523 | 534 |
"rop"=>$rop,"assembly"=>$assembly, |
524 | 535 |
"shop"=>$shop,"makemodel"=>$makemodel),$pricegroup |
525 | 536 |
); |
526 |
if ($hersteller>0 && $model) { |
|
537 |
if ($hersteller>0 ) { // && $model) {
|
|
527 | 538 |
$partsid=getPartsid($db,$zeile[$fldpos["partnumber"]]); |
528 |
if ($partsid) { |
|
529 |
getMakemodel($db,$check,$hersteller,$model,$partsid,true); |
|
539 |
if ($partsid) {
|
|
540 |
getMakemodel($db,$check,$hersteller,$model,$partsid,$lastcost,true);
|
|
530 | 541 |
} |
531 | 542 |
} |
532 | 543 |
unset($zeile); |
Auch abrufbar als: Unified diff
Diverse Erweiterungen und ein wenig Aufräumen.