se/SE/se-lib/Route/UrlAction/VoIPAmsImport.php

<?php

Lib::loadClass('RouteBase');
Lib::loadClass('ProcesHelper');

class Route_UrlAction_VoIPAmsImport extends RouteBase {

	private $fields = [
		"Data początkowa" => [
			"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.'] != '27') 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;
?>
<div class="container" style="margin-top:20px">
  <form class="form-horizontal" method="post" enctype="multipart/form-data">
    <legend>
      Znalezione rekordy billingowe do zafakurowania
    </legend>
    <div class="col-sm-offset-2 col-sm-8">
       <div style="text-align:center;">
         <input type="hidden" name="csv" value="<?=$csvPost?>"/>
         <button type="submit" class="btn btn-primary" name="action" value="doImportCsv">Importuj</button>
       </div>
       <hr/>
       <table class="table table-bordered table-hover table-striped">
         <thead>
            <tr style="text-align:center; background-color:lightgray"><td width="19%">Lp.</td><td>Data połączenia</td><td>Numer klienta</td><td>Koszt netto połączenia</td></tr>
         </thead>
         <tbody>
<?php
		foreach ($csv as $i => $v) {
			echo "<tr><td>" . ($i + 1) . "</td>";
			foreach ($this->fields as $field) echo "<td> " . $v[$field["name"]] . "</td>";
			echo "</tr>\n";
		}
?>
        </tbody>
      </table>
    </div>
  </form>
</div>
<?php
	}

	private function doQuery($query) {
		try {
			return DB::getPDO()->query($query);
		} catch (Exception $e) {
			throw new Exception("Wystąpił problem z zapytaniem SQL: {$query}<br/>{$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[] = <<<EOT
create temporary table `tmp_VoIPAmsImport2`
select sesa.ID_SERVICES, sesa.ID_BILLING_USERS, '16' as TYPE, DATE_FORMAT(tmp.`date`,'%Y-%m') as VALUE1, sum(round(tmp.`cost`,2)) as VALUE2,
	'1' as NODE_ID, NULL as EXTERNAL_ID,'0' as TAB_UPDATE_STAT, 'ZAIMPORTOWANO PRZEZ VoIPAmsImport' as TAB_UPDATE_INFO
from `tmp_VoIPAmsImport` tmp left join SES_VOIP_A sesa on sesa.S_ALIAS=tmp.number
where sesa.ID_SERVICES IS NOT NULL
group by sesa.ID_SERVICES, VALUE1
having VALUE2>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 = <<<EOT
insert into SES_VOIP_C (ID_SERVICES, ID_BILLING_USERS, TYPE, VALUE1, VALUE2, NODE_ID, EXTERNAL_ID, TAB_UPDATE_STAT, TAB_UPDATE_INFO)
select * from `tmp_VoIPAmsImport2`
EOT;
		$this->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() {
?>
<div class="container" style="margin-top:20px">
  <form class="form-horizontal" method="post" enctype="multipart/form-data">
    <legend>
      Importowanie danych billingowych połączeń telefonicznych z platformy AMS
    </legend>
      <label class="col-sm-2 control-label">Wgraj plik</label>
      <div class="col-sm-10" style="margin-top:3px;">
        <input type="hidden" name="action" value="importCsv"/>
        <input type="file" name="csv" onchange="javascript:this.form.submit();"/>
      </div>
    </div>
  </form>
</div>
<?php
	}

	public function defaultAction() {
		SE_Layout::gora();
		SE_Layout::menu();
		try {
			$action = V::get('action','',$_POST);
			$actions = [
				"importCsv",
				"doImportCsv"
			];
			if (in_array($action, $actions)) $this->$action();
			else $this->form();
		} catch (Exception $e) {
			SE_Layout::alert('danger',$e->getMessage());
			$this->form();
		}
		SE_Layout::dol();
	}
}