se/SE/schema/WPS_Functions/BI_audit/1_rum_create_ref.php

#!/usr/bin/env php
<?php
$_SERVER['SERVER_NAME'] = "biuro.biall-net.pl";
require("/Library/Server/Web/Data/Sites/Default/dev-bzyk/se-lib/bootstrap.php");
#require("/Library/Server/Web/Data/Sites/SE-producition-git/SE/se-lib/bootstrap.php");
date_default_timezone_set('Europe/Warsaw');// PHP 5 >= 5.1.0 required by date functions


function is_nip($nip) {
	$waga = [6, 5, 7, 2, 3, 4, 5, 6, 7];
	$c = 0;
	for ($i = 0; $i < 9; $i++) $c += $nip[$i] * $waga[$i];
	$c = ($c % 11) % 10;
	if ($nip[9] == $c) return true;
	else return false;
}

function is_regon($regon) {
	$waga = [8, 9, 2, 3, 4, 5, 6, 7];
	$c = 0;
	for ($i = 0; $i < 8; $i++) $c += $regon[$i] * $waga[$i];
	$c = ($c % 11) % 10;
	if ($regon[8] == $c) return true;
	else return false;
}

function parseNipRegon($string) {
	preg_match('/^(.*[^[:digit:]])?([[:digit:]]{10})([^[:digit:]].*)?$/', str_replace('-', '', $string), $matches);
	if ($matches && is_nip($matches[2])) $nip = $matches[2];
	else $nip = null;

	preg_match('/^(.*[^[:digit:]])?([[:digit:]]{9})([^[:digit:]].*)?$/', str_replace('-', '', $string), $matches);
	if ($matches && is_regon($matches[2])) $regon = $matches[2];
	else $regon = null;

	return [$nip, $regon];
}

                                        
$fields['BI_audit_ENERGA_RUM_KONTRAHENCI'] = [
	'Tytul_dokumentu',
	'Pelna_nazwa_kontrahenta',
	'NIP',
	'REGON',
	'KRS',
	'Kraj',
	'Kod_pocztowy',
	'Miejscowosc',
	'Ulica',
	'Numer_budynku',
	'Numer_mieszkania_lokalu',
	'TERYT_SYM',
	'TERYT_SYM_UL',
];

$fields['BI_audit_KRS'] = [
	'"Dodano z BI_audit_KRS"',
	'nazwa',
	'nip',
	'regon',
	'krs',
	'A_kraj',
	'A_kod',
	'A_miejscowosc',
	'A_ulica',
	'A_nrDomu',
	'A_nrLokalu',
	'TERYT_SYM',
	'TERYT_SYM_UL',
];

$fields['BI_audit_CEIDG'] = [
	'"Dodano z BI_audit_CEIDG"',
	'firma',
	'nip',
	'regon',
	'null',
	'kraj',
	'kodPocztowy',
	'miejscowosc',
	'ulica',
	'budynek',
	'lokal',
	'TERYT_SYM',
	'TERYT_SYM_UL',
];

DB::getPDO()->query("truncate table `CRM__#REF_TABLE__23`");
$query = "select ID, Strony_umowy from `BI_audit_ENERGA_RUM_UMOWY`";
$result = DB::query($query);
while ($row = DB::fetch($result)) {
	$ID = $row->ID;
	$strony = array_map('trim', explode("|", $row->Strony_umowy));
	$K_IDs = [];
	foreach ($strony as $item) {
		$strona = array_map('trim', explode(";", $item));
		$nr = null;
		if (count($strona) == 3) $i = 1;//$nr = $strona[1];
		elseif (count($strona) == 4) $i = 2;//$nr = $strona[2];
		elseif (count($strona) >= 5 && count($strona) <= 6) $i = 3;//$nr = $strona[3];
		else $i = null;
		if ($i) $nr = $strona[$i];
		$notFound = false;
		if (is_numeric($nr)) {
			$query = "select ID from BI_audit_ENERGA_RUM_KONTRAHENCI where Numer_kontrahenta = '{$nr}'";
			if ($K_ID = DB::getPDO()->fetchValue($query)) {
				$K_IDs[] = $K_ID;
			} else {
				$nazwa = $strona[$i+1];
				$query = "select ID from BI_audit_ENERGA_RUM_KONTRAHENCI where Pelna_nazwa_kontrahenta = '" . addslashes($nazwa) . "'";
				if ($result2 = DB::getPDO()->fetchall($query)) {
					foreach ($result2 as $item) $K_IDs[] = $item['ID'];
				} else $notFound = true;
			}
		} else $notFound = true;
		if ($notFound) {
			list($nip, $regon) = parseNipRegon($item);
			if ($nip || $regon) {
				$query = "select ID from BI_audit_ENERGA_RUM_KONTRAHENCI where NIP = '{$nip}' or REGON = '{$regon}'";
				if ($result2 = DB::getPDO()->fetchall($query)) {
					foreach ($result2 as $item) $K_IDs[] = $item['ID'];
				} else {
					$query = "select ID from BI_audit_KRS where nip = '{$nip}' or regon = '{$regon}'";
					if ($result2 = DB::getPDO()->fetchall($query)) {
						foreach ($result2 as $item) {
							$query = "insert into BI_audit_ENERGA_RUM_KONTRAHENCI (" . implode(', ', $fields['BI_audit_ENERGA_RUM_KONTRAHENCI']) . ")" .
								" select " . implode(', ', $fields['BI_audit_KRS']) . " from BI_audit_KRS where ID = '{$item['ID']}'";
							if (DB::getPDO()->query($query)) $K_IDs[] = DB::getPDO()->lastInsertId();
						}
					} else {
						$query = "select ID from BI_audit_CEIDG where nip = '{$nip}' or regon = '{$regon}'";
						if ($result2 = DB::getPDO()->fetchall($query)) {
							foreach ($result2 as $item) {
								$query = "insert into BI_audit_ENERGA_RUM_KONTRAHENCI (" . implode(', ', $fields['BI_audit_ENERGA_RUM_KONTRAHENCI']) . ")" .
									" select " . implode(', ', $fields['BI_audit_CEIDG']) . " from BI_audit_CEIDG where ID = '{$item['ID']}'";
								if (DB::getPDO()->query($query)) $K_IDs[] = DB::getPDO()->lastInsertId();
							}
						}
					}
				}
			}
		}
	}
	foreach ($K_IDs as $K_ID) {
		$query = "insert into `CRM__#REF_TABLE__23` (PRIMARY_KEY, REMOTE_PRIMARY_KEY) values ('{$ID}', '{$K_ID}')";
		DB::getPDO()->query($query);
	}
}