[ "name" => "date", "regex" => "/^[[:digit:]]{4}(-[[:digit:]]{2}){2} [[:digit:]]{2}(:[[:digit:]]{2}){2}$/", "sqlType" => "datetime", ], "Numer abonenta" => [ "name" => "number", "regex" => "/^[[:digit:]]{11}$/", "sqlType" => "varchar(11)", ], "Koszt netto" => [ "name" => "cost", "regex" => "/^[[:digit:]]+,[[:digit:]]{2}$/", "sqlType" => "decimal(10,2)", "replace" => [ "from" => ",", "to" => ".", ], ], ]; public function handleAuth() { if (!User::logged()) { throw new HttpException('Unauthorized', 401); } } private function parseCsv($file) { if (!file_exists($file)) Throw new Exception("CSV file error #1 - File not found."); $csv = array_map(function ($line) { return str_getcsv(trim($line), ";"); }, file($file)); array_walk($csv, function(&$a) use ($csv) { if (count($csv[0]) != count($a)) throw new Exception("CSV file error #2 - Wrong fields count."); $a = array_combine($csv[0], $a); }); array_shift($csv); $csv = array_filter($csv, function($a) { if (($a['TG wych.'] == '-1') || (!$a['TG wych.'])) return false; if ($a['Koszt netto'] == '0,00') return false; return true; }); array_walk($csv, function(&$a) { $result = []; foreach ($this->fields as $k => $v) { if (!isset($a[$k])) throw new Exception("CSV file error #3 - Field \"{$k}\" not found."); if (!preg_match($v["regex"], $a[$k])) throw new Exception("CSV file error #4 - Bad value of \"{$k}\" ({$a[$k]})."); if (isset($v["replace"])) $result[$v["name"]] = str_replace($v["replace"]["from"], $v["replace"]["to"], $a[$k]); else $result[$v["name"]] = $a[$k]; } $a = $result; }); return array_values($csv); } private function importCsv() { if (!isset($_FILES['csv'])) throw new Exception("Błąd formularza"); if ($_FILES['csv']['type'] != "text/csv") throw new Exception("Błędny plik CSV"); $csv = $this->parseCsv($_FILES['csv']['tmp_name']); $csvPost = base64_encode(gzcompress(json_encode($csv))); $_SESSION['VoIPAmsImportReady'] = true; ?>
Znalezione rekordy billingowe do zafakurowania
$v) { echo ""; foreach ($this->fields as $field) echo ""; echo "\n"; } ?>
Lp.Data połączeniaNumer klientaKoszt netto połączenia
" . ($i + 1) . " " . $v[$field["name"]] . "
query($query); } catch (Exception $e) { throw new Exception("Wystąpił problem z zapytaniem SQL: {$query}
{$e->getMessage()}"); } } private function doImportCsv() { if (!isset($_SESSION['VoIPAmsImportReady'])) throw new Exception("Błąd formularza"); unset($_SESSION['VoIPAmsImportReady']); $csv = json_decode(gzuncompress(base64_decode(V::get('csv','',$_POST))), true); if (!$csv) throw new Exception("Błąd formularza"); $createColumns = []; $keys = []; $columns = []; foreach ($this->fields as $field) { $createColumns[] = "`{$field['name']}` {$field['sqlType']} NOT NULL"; $keys[] = "KEY `{$field['name']}` (`{$field['name']}`)"; $columns[] = $field['name']; } $queries = []; $queries[] = "create temporary table `tmp_VoIPAmsImport` (" . implode(", ", array_merge($createColumns, $keys)) . ")"; foreach ($csv as $v) { $values = []; foreach ($this->fields as $field) $values["`{$field['name']}`"] = "'{$v[$field['name']]}'"; $queries[] = "insert into `tmp_VoIPAmsImport` (" . implode(", ", array_keys($values)) . ") values (" . implode(", ", $values) . ")"; } $queries[] = <<0 EOT; foreach ($queries as $query) $this->doQuery($query); $result = DB::getPDO()->fetchFirst("select count(*) as `count`, sum(VALUE2) as `sum` from `tmp_VoIPAmsImport2`"); $sumFromCsv = DB::getPDO()->fetchValue("select sum(`cost`) from `tmp_VoIPAmsImport`"); $query = <<doQuery($query); SE_Layout::alert('success', "Z powodzeniem zaimportowano " . count($csv) . " rekordów billingowych ({$result['count']} pozycji do faktur na łączną kwotę {$result['sum']} zł netto; suma wynikająca z pliku CSV - {$sumFromCsv})"); $this->form(); } private function form() { ?>
Importowanie danych billingowych połączeń telefonicznych z platformy AMS
$action(); else $this->form(); } catch (Exception $e) { SE_Layout::alert('danger',$e->getMessage()); $this->form(); } SE_Layout::dol(); } }