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); |
Auch abrufbar als: Unified diff
Diverse Erweiterungen und ein wenig Aufräumen.