<?php

class Encoding {

	protected static $UTFList = array(
		185 => "ą",
		165 => "Ą",
		234 => "ę",
		140 => "Ś",
		179 => "ł",
		163 => "Ł",
		209 => "Ń",
		241 => "ń",
		243 => "ó",
		191 => "ż",
		159 => "ź",
		175 => "Ż",
		156 => "ś",
		211 => "Ó",
		202 => "Ę"
	);

	static function toUTF8($text) {
		if (is_array($text)) {
			foreach ($text as $k => $v) {
				$text[$k] = self::toUTF8($v);
			}
			return $text;
		}
		if (!is_string($text)) {
			return $text;
		}
		$max = strlen($text);
		$buf = "";
		$tmp = str_split($text);
		foreach ($tmp as $key => $value) {
			$char = '';
			foreach (self::$UTFList as $klucz => $wartosc) {
				if (ord($value) == $klucz) {
					$char = $wartosc;
				}
			}
			if ($char != '') {
				$buf .= $char;
			} else {
				$buf .= $value;
			}
		}
		return trim($buf);
	}

}

//Tworze polaczenie z baza
ini_set('mssql.charset', 'Windows-1251');
$serverName = "192.168.1.26"; //serverName\instanceName
$connectionInfo = array("Database" => "CDN_MATINSTAL", "UID" => "b2b", "PWD" => "b2b2015");
$msconn = mssql_connect("192.168.1.26\OPTIMA", "b2b", "b2b2015");
if ($msconn) {
	echo "Ustanowiono polaczenie z MSSQL." . PHP_EOL;
} else {
	echo "Nie można było ustawić połączenia." . PHP_EOL;
	die(print_r(mssql_errors(), true));
}

$myconn = mysql_connect('localhost', 'root', 'sgpmk777');
if (!$myconn) {
	echo "Nie można było ustawić połączenia z MySQL." . PHP_EOL;
	mssql_close($msconn);
	die(mysql_error());
} else {
	echo 'Ustanowiono polaczenie z MySQL.' . PHP_EOL;
}

if (mysql_select_db('crm')) {
	echo 'MySQL: Pomyślnie wybrano bazę danych CRM' . PHP_EOL;
} else {
	echo 'MySQL: Błąd przy wybieraniu bazy danych CRM' . PHP_EOL;
	mysql_close($myconn);
	mssql_close($msconn);
	die();
}

mysql_query('SET NAMES utf8');

if (mysql_set_charset('utf8')) {
	echo 'MySQL: Ustawiono kodowanie ' . mysql_client_encoding() . PHP_EOL;
} else {
	echo 'MySQL: Błąd w ustawianiu kodowania UTF-8' . PHP_EOL;
	die(mysql_error());
}

echo 'Dodawanie form platnosci' . PHP_EOL;
$query = 'SELECT * FROM [CDN_MATINSTAL].[CDN].[FormyPlatnosci]';
$stmt = mssql_query($query);

if ($stmt === false) {
	echo "Blad w przetwarzaniu zapytania";
	mysql_close($myconn);
	mssql_close($msconn);
	die(print_r(mssql_errors(), true));
}

$query = 'SELECT id FROM ecmpaymentconditions';
$return = mysql_query($query);
$exist = array();
while ($rr = mysql_fetch_assoc($return)) {
	$exist[] = $rr['id'];
}
$insert = 0;
$update = 0;
while ($row = mssql_fetch_assoc($stmt)) {
	if (in_array($row['FPl_FPlId'], $exist)) {
		$update++;
		$typplatnosci = '';
		if ($value['FPl_Typ'] == '1') {
			$typplatnosci = '1';
		} else {
			$typplatnosci = '0';
		}
		$query = 'UPDATE ecmpaymentconditions SET ';
		$query .= " name = '" . Encoding::toUTF8($row['FPl_Nazwa'], "UTF-8", "Windows-1251") . "',";
		$query .= " date_modified = NOW(),";
		$query .= " deleted = '" . $row['FPl_NieAktywny'] . "',";
		$query .= " days = '" . $row['FPl_Termin'] . "',";
		$query .= " discount = '" . $row['FPl_Rabat'] . "',";
		$query .= " payment_method = '" . $typplatnosci . "'";
		$query .= " WHERE id = '" . $row['FPl_FPlId'] . "'";
	} else {
		$insert++;
		$typplatnosci = '';
		if ($value['FPl_Typ'] == '1') {
			$typplatnosci = '1';
		} else {
			$typplatnosci = '0';
		}
		$query = "INSERT ecmpaymentconditions (
			id,
			name,
			date_entered,
			date_modified,
			modified_user_id,
			assigned_user_id,
			created_by,
			deleted,
			days,
			discount,
			discount_days,
			payment_method)
			VALUES (" .
				$row['FPl_FPlId'] . "," . //id
				"'" . Encoding::toUTF8($row['FPl_Nazwa'], "UTF-8", "Windows-1251") . "'" . "," . //name
				"NOW()" . "," . //date_entered
				"NOW()" . "," . //date_modified
				"'243170e7-70c9-99c8-6ef3-542a4ff7e733'" . "," . //modified_user_id
				"'243170e7-70c9-99c8-6ef3-542a4ff7e733'" . "," . //assigned_user_id
				"'243170e7-70c9-99c8-6ef3-542a4ff7e733'" . "," . //created_by
				$row['FPl_NieAktywny'] . "," . //deleted
				$row['FPl_Termin'] . "," . //days
				$row['FPl_Rabat'] . "," . //discount
				"'0'" . "," . //discount_days
				$typplatnosci . ");"; //payment_method
		mysql_query($query);
	}
}
echo '*** Zaktualizowano ' . $update . PHP_EOL;
echo '*** Dodano ' . $insert . PHP_EOL;
echo 'Zakonczono import form platnosci' . PHP_EOL;

echo 'Wczytywanie kontrahentów' . PHP_EOL;

$query = "SELECT * FROM [CDN_MATINSTAL].[CDN].[Kontrahenci]";

$stmt = mssql_query($query);
if ($stmt === false) {
	echo "Error in query preparation/execution.\n";
	die(print_r(mssql_errors(), true));
}

$query = 'DELETE FROM accounts_websites';
$return = mysql_query($query);
$query = 'DELETE FROM account_banks';
$return = mysql_query($query);

$query = 'SELECT id FROM accounts';
$return = mysql_query($query);
$existAccounts = array();
while ($rr = mysql_fetch_assoc($return)) {
	$existAccounts[] = $rr['id'];
}
$insert = 0;
$update = 0;

$urlID = 0;
$bankAccountID = 0;
while ($row = mssql_fetch_array($stmt)) {
	if (in_array($row['Knt_KntId'], $existAccounts)) {
		$update++;
		if ($row['Knt_FplID'] != '') {
			$q = "Select name from ecmpaymentconditions where id = " . $row['Knt_FplID'] . ";";
			$ecmpaymentconditions_name = mysql_fetch_assoc(mysql_query($q));
			$ecmpaymentconditions_name = $ecmpaymentconditions_name['name'];
		}
		$accountName = '';
		$accountName .= trim(Encoding::toUTF8($row['Knt_Nazwa1'])) . ' ';
		$accountName .= trim(Encoding::toUTF8($row['Knt_Nazwa2'])) . ' ';
		$accountName .= trim(Encoding::toUTF8($row['Knt_Nazwa3']));
		$accountName = trim($accountName);

		$accountStreet = '';
		$accountStreet .= trim(Encoding::toUTF8($row['Knt_Ulica'])) . ' ';
		$accountStreet .= trim(Encoding::toUTF8($row['Knt_NrDomu'])) . ' ';
		$accountStreet .= trim(Encoding::toUTF8($row['Knt_NrLokalu']));
		$accountStreet = trim($accountStreet);

		$accountKorStreet = '';
		$accountKorStreet .= trim(Encoding::toUTF8($row['Knt_KorUlica'])) . ' ';
		$accountKorStreet .= trim(Encoding::toUTF8($row['Knt_KorNrDomu'])) . ' ';
		$accountKorStreet .= trim(Encoding::toUTF8($row['Knt_KorNrLokalu']));
		$accountKorStreet = trim($accountKorStreet);

		if (strlen($row['Knt_URL']) > 1) {
			$q = "INSERT INTO accounts_websites VALUES (";
			$q .= "'" . $urlID . "',";
			$q .= "'" . $row['Knt_KntId'] . "',";
			$q .= "'0',";
			$q .= "NOW(),";
			$q .= "'1',";
			$q .= "'" . $row['Knt_URL'] . "',";
			$q .= "'0',";
			$q .= "NOW(),'243170e7-70c9-99c8-6ef3-542a4ff7e733');";
			mysql_query($q);
		}

		if (strlen($row['Knt_RachunekNr']) > 1) {
			$q = "INSERT INTO account_banks VALUES (";
			$q .= "'" . $bankAccountID++ . "',";
			$q .= "NOW(),";
			$q .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',";
			$q .= "'" . $row['Knt_RachunekNr'] . "',";
			$q .= "'0',";
			$q .= "'" . $row['Knt_RachunekNr'] . "',";
			$q .= "'0',";
			$q .= "'" . $row['Knt_KntId'] . "');";
			mysql_query($q);
		}
		$query = "UPDATE accounts SET ";
		$query .= "date_modified=NOW(),";
		$query .= "modified_user_id = '243170e7-70c9-99c8-6ef3-542a4ff7e733',";
		$query .="index_dbf='" . Encoding::toUTF8($row['Knt_Kod']) . "',";
		$query .="name='" . $accountName . "',";
		$query .="register_address_country='" . Encoding::toUTF8($row['Knt_Kraj']) . "',";
		$query .="register_address_state='" . Encoding::toUTF8($row['Knt_Wojewodztwo']) . "',";
		$query .="register_address_street='" . $accountStreet . "',";
		$query .="register_address_city='" . Encoding::toUTF8($row['Knt_Miasto']) . "',";
		$query .="register_address_postalcode='" . Encoding::toUTF8($row['Knt_KodPocztowy']) . "',";
		$query .="to_vatid_unformated='" . Encoding::toUTF8($row['Knt_NipE']) . "',";
		$query .="to_vatid='" . Encoding::toUTF8($row['Knt_Nip']) . "',";
		$query .="regon='" . Encoding::toUTF8($row['Knt_Regon']) . "',";
		$query .="phone_office='" . Encoding::toUTF8($row['Knt_Telefon1']) . "',";
		$query .="phone_fax='" . Encoding::toUTF8($row['Knt_Fax']) . "',";
		$query .="krs='" . Encoding::toUTF8($row['Knt_Zezwolenie']) . "',";
		$query .="ecmpaymentcondition_id='" . Encoding::toUTF8($row['Knt_FplID']) . "',";
		$query .="ecmpaymentcondition_name='" . $ecmpaymentconditions_name . "',";
		$query .="billing_address_street='" . $accountKorStreet . "',";
		$query .="billing_address_city='" . Encoding::toUTF8($row['Knt_KorMiasto']) . "',";
		$query .="billing_address_postalcode='" . Encoding::toUTF8($row['Knt_KorKodPocztowy']) . "',";
		$query .="billing_address_country='" . Encoding::toUTF8($row['Knt_KorKraj']) . "'";
		$query .= " WHERE id='" . $row['Knt_KntId'] . "'";
		mysql_query($query);
	} else {
		$insert++;
		if ($row['Knt_FplID'] != '') {
			$q = "Select name from ecmpaymentconditions where id = " . $row['Knt_FplID'] . ";";
			$ecmpaymentconditions_name = mysql_fetch_assoc(mysql_query($q));
			$ecmpaymentconditions_name = $ecmpaymentconditions_name['name'];
		}
		$accountName = '';
		$accountName .= trim(Encoding::toUTF8($row['Knt_Nazwa1'])) . ' ';
		$accountName .= trim(Encoding::toUTF8($row['Knt_Nazwa2'])) . ' ';
		$accountName .= trim(Encoding::toUTF8($row['Knt_Nazwa3']));
		$accountName = trim($accountName);

		$accountStreet = '';
		$accountStreet .= trim(Encoding::toUTF8($row['Knt_Ulica'])) . ' ';
		$accountStreet .= trim(Encoding::toUTF8($row['Knt_NrDomu'])) . ' ';
		$accountStreet .= trim(Encoding::toUTF8($row['Knt_NrLokalu']));
		$accountStreet = trim($accountStreet);

		$accountKorStreet = '';
		$accountKorStreet .= trim(Encoding::toUTF8($row['Knt_KorUlica'])) . ' ';
		$accountKorStreet .= trim(Encoding::toUTF8($row['Knt_KorNrDomu'])) . ' ';
		$accountKorStreet .= trim(Encoding::toUTF8($row['Knt_KorNrLokalu']));
		$accountKorStreet = trim($accountKorStreet);

		if (strlen($row['Knt_URL']) > 1) {
			$q = "INSERT INTO accounts_websites VALUES (";
			$q .= "'" . $urlID . "',";
			$q .= "'" . $row['Knt_KntId'] . "',";
			$q .= "'0',";
			$q .= "NOW(),";
			$q .= "'1',";
			$q .= "'" . $row['Knt_URL'] . "',";
			$q .= "'0',";
			$q .= "NOW(),'243170e7-70c9-99c8-6ef3-542a4ff7e733');";
			mysql_query($q);
		}

		if (strlen($row['Knt_RachunekNr']) > 1) {
			$q = "INSERT INTO account_banks VALUES (";
			$q .= "'" . $bankAccountID++ . "',";
			$q .= "NOW(),";
			$q .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',";
			$q .= "'" . $row['Knt_RachunekNr'] . "',";
			$q .= "'0',";
			$q .= "'" . $row['Knt_RachunekNr'] . "',";
			$q .= "'0',";
			$q .= "'" . $row['Knt_KntId'] . "');";
			mysql_query($q);
		}

		$query = "INSERT accounts (
				id,
				date_entered,
				date_modified,
				modified_user_id,
				created_by,
				assigned_user_id,
				index_dbf,
				name,
				register_address_country,
				register_address_state,
				register_address_street,
				register_address_city,
				register_address_postalcode,
				to_vatid_unformated,
				to_vatid,
				regon,
				phone_office,
				phone_fax,
				krs,
				ecmpaymentcondition_id,
				ecmpaymentcondition_name,
				billing_address_street,
				billing_address_city,
				billing_address_postalcode,
				billing_address_country) VALUES (";
		$query .= $row['Knt_KntId'] . ","; // id
		$query .= "NOW(),"; // date_entered
		$query .= "NOW(),"; // date_modified
		$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //modified_user_id
		$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //created_by
		$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //created_by
		$query .= "'" . Encoding::toUTF8($row['Knt_Kod']) . "',"; // index_dbf
		$query .= "'" . $accountName . "',"; // name
		$query .= "'" . Encoding::toUTF8($row['Knt_Kraj']) . "',"; // register_address_country
		$query .= "'" . Encoding::toUTF8($row['Knt_Wojewodztwo']) . "',"; // register_address_state
		$query .= "'" . $accountStreet . "',"; // register_address_street
		$query .= "'" . Encoding::toUTF8($row['Knt_Miasto']) . "',"; // register_address_city
		$query .= "'" . Encoding::toUTF8($row['Knt_KodPocztowy']) . "',"; // register_address_postalcode
		$query .= "'" . Encoding::toUTF8($row['Knt_NipE']) . "',";  // to_vatid_unformated
		$query .= "'" . Encoding::toUTF8($row['Knt_Nip']) . "',"; //to_vatid
		$query .= "'" . Encoding::toUTF8($row['Knt_Regon']) . "',"; //regon
		$query .= "'" . Encoding::toUTF8($row['Knt_Telefon1']) . "',"; // phone office
		$query .= "'" . Encoding::toUTF8($row['Knt_Fax']) . "',"; //phone fax
		$query .= "'" . Encoding::toUTF8($row['Knt_Zezwolenie']) . "',"; //krs
		$query .= "'" . Encoding::toUTF8($row['Knt_FplID']) . "',"; // ecmpaymentcondition_id
		$query .= "'" . $ecmpaymentconditions_name . "',"; //ecmpaymentcondition_name
		$query .= "'" . $accountKorStreet . "',"; // billing_address_street
		$query .= "'" . Encoding::toUTF8($row['Knt_KorMiasto']) . "',"; // billing_address_city
		$query .= "'" . Encoding::toUTF8($row['Knt_KorKodPocztowy']) . "',"; // billing_address_postalcode
		$query .= "'" . Encoding::toUTF8($row['Knt_KorKraj']) . "'"; // billing_address_country
		$query .= ");";
		mysql_query($query);
	}
}
echo '*** Zaktualizowano ' . $update . PHP_EOL;
echo '*** Dodano ' . $insert . PHP_EOL;
echo 'Zakonczono import kontrahentow' . PHP_EOL;


echo 'Dodaje magazyny' . PHP_EOL;
$query = "SELECT * FROM [CDN_MATINSTAL].[CDN].[Magazyny]";
$stmt = mssql_query($query);
if ($stmt === false) {
	echo "Error in query preparation/execution.\n";
	die(print_r(mssql_errors(), true));
}

$query = 'SELECT id FROM ecmstocks';
$return = mysql_query($query);
$existEcmStocks = array();
while ($rr = mysql_fetch_assoc($return)) {
	$existEcmStocks[] = $rr['id'];
}
$insert = 0;
$update = 0;


$no = 0;
while ($row = mssql_fetch_array($stmt)) {
	if (in_array($row['Mag_MagId'], $existEcmStocks)) {
		$update++;
		$query = "UPDATE ecmstocks SET ";
		$query .="name='" . Encoding::toUTF8($row['Mag_Nazwa']) . "',";
		$query .="deleted='" . $row['Mag_NieAktywny'] . "',";
		$query .="date_modified=NOW(),";
		$query .="modified_user_id='243170e7-70c9-99c8-6ef3-542a4ff7e733',";
		$query .="description='" . Encoding::toUTF8($row['Mag_Opis']) . "',";
		$query .="no='" . $no++ . "'";
		$query .= " WHERE id='" . $row['Mag_MagId'] . "'";
		mysql_query($query);
	} else {
		$insert++;
		try {
			$tz = new DateTimeZone("Europe/Warsaw");
			$dateZal = new DateTime($row['Mag_TS_Zal'], $tz);
			$dateMod = new DateTime($row['Mag_TS_Mod'], $tz);
		} catch (Exception $e) {
			echo $e->getMessage();
			exit(1);
		}

		$query = "INSERT ecmstocks (
				id,
				name,
				deleted,
				date_entered,
				date_modified,
				modified_user_id,
				assigned_user_id,
				created_by,
				description,
				main,
				no
				) VALUES (";
		$query .= "'" . $row['Mag_MagId'] . "',"; // id
		$query .= "'" . Encoding::toUTF8($row['Mag_Nazwa']) . "',"; // name
		$query .= "'" . $row['Mag_NieAktywny'] . "',"; // deleted
		$query .= "'" . $dateZal->format('Y-m-d') . "'" . ","; //date_entered
		$query .= "'" . $dateMod->format('Y-m-d') . "'" . ","; //date_entered
		$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //modified_user_id
		$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //assigned_user_id
		$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //created_by
		$query .= "'" . Encoding::toUTF8($row['Mag_Opis']) . "',"; // description
		$query .= "'0',"; //main
		$query .= "'" . $no++ . "'"; // no
		$query .= ");";
		mysql_query($query);
	}
}
echo '*** Zaktualizowano ' . $update . PHP_EOL;
echo '*** Dodano ' . $insert . PHP_EOL;
echo 'Zakonczono dodawanie magazynow' . PHP_EOL;

echo 'Dodawanie towarow' . PHP_EOL;
$query = "SELECT Twr_TwrId, Twr_JM, Twr_Stawka, Twr_Nazwa, Twr_Kod, Twr_NieAktywny FROM [CDN_MATINSTAL].[CDN].[Towary]";
$stmt = mssql_query($query);
if ($stmt === false) {
	echo "Error in query preparation/execution.\n";
	die(print_r(mssql_errors(), true));
}

$query = 'SELECT id FROM ecmproducts';
$return = mysql_query($query);
$existEcmProducts = array();
while ($rr = mysql_fetch_assoc($return)) {
	$existEcmProducts[] = $rr['id'];
}
$insert = 0;
$update = 0;
$re = mysql_query("SELECT id, name, value FROM ecmvats WHERE deleted='0' ORDER BY value asc");
$vatList = array();
while ($row = mysql_fetch_assoc($re)) {
	$tmp = array();
	$tmp['id'] = $row['id'];
	$tmp['name'] = $row['name'];
	$tmp['value'] = $row['value'];
	$vatList[$row['value']] = $tmp;
}

while ($row = mssql_fetch_array($stmt)) {
	if (in_array($row['Twr_TwrId'], $existEcmProducts)) {
		$update++;
		$vacikID = $vatList[$row['Twr_Stawka'] . '.00']['id'];
		$vacikNAME = $vatList[$row['Twr_Stawka'] . '.00']['name'];
		$vacikVALUE = $vatList[$row['Twr_Stawka'] . '.00']['value'];
		$jm = null;
		$jmtmp = Encoding::toUTF8($row['Twr_JM']);
		switch ($jmtmp) {
			case 'usі.':
				$jm = 1;
				break;
			case 't':
				$jm = 2;
				break;
			case 'szt':
				$jm = 3;
				break;
			case 'kpl.':
				$jm = 4;
				break;
			case 'kg':
				$jm = 5;
				break;
			case 'litr':
				$jm = 6;
				break;
			case 'm':
				$jm = 7;
				break;
			case 'godz':
				$jm = 8;
				break;
			case 'km.':
				$jm = 9;
				break;
		}
		$query = "UPDATE ecmproducts SET ";
		$query .= "name='" . Encoding::toUTF8($row['Twr_Nazwa']) . "',"; // name
		$query .= "code='" . Encoding::toUTF8($row['Twr_Kod']) . "',"; // code
		$query .= "deleted=0,";
		$query .= "product_active='" . Encoding::toUTF8($row['Twr_NieAktywny']) . "',";
		$query .= "vat_value='" . $vacikVALUE . "',"; // vat_value
		$query .= "vat_id='" . $vacikID . "',"; // vat_value
		$query .= "vat_name='" . $vacikNAME . "',"; // vat_value
		$query .= "date_modified=NOW(),";
		$query .= "modified_user_id='243170e7-70c9-99c8-6ef3-542a4ff7e733',";
		$query .= "unit_id='" . $jm . "'";
		$query .= " WHERE id= '" . $row['Twr_TwrId'] . "'";
		mysql_query($query);
	} else {
		$insert++;
		$vacikID = $vatList[$row['Twr_Stawka'] . '.00']['id'];
		$vacikNAME = $vatList[$row['Twr_Stawka'] . '.00']['name'];
		$vacikVALUE = $vatList[$row['Twr_Stawka'] . '.00']['value'];
		$jm = null;
		$jmtmp = Encoding::toUTF8($row['Twr_JM']);
		switch ($jmtmp) {
			case 'usі.':
				$jm = 1;
				break;
			case 't':
				$jm = 2;
				break;
			case 'szt':
				$jm = 3;
				break;
			case 'kpl.':
				$jm = 4;
				break;
			case 'kg':
				$jm = 5;
				break;
			case 'litr':
				$jm = 6;
				break;
			case 'm':
				$jm = 7;
				break;
			case 'godz':
				$jm = 8;
				break;
			case 'km.':
				$jm = 9;
				break;
		}

		$query = "INSERT ecmproducts (
				id,
				name,
				code,
				deleted,
				product_active,
				vat_value,
				vat_id,
				vat_name,
				date_entered,
				date_modified,
				modified_user_id,
				assigned_user_id,
				created_by,
				unit_id
				) VALUES (";
		$query .= $row['Twr_TwrId'] . ","; // id
		$query .= "'" . Encoding::toUTF8($row['Twr_Nazwa']) . "',"; // name
		$query .= "'" . Encoding::toUTF8($row['Twr_Kod']) . "',"; // code
		$query .= "0,"; // deleted
		$query .= "'" . Encoding::toUTF8($row['Twr_NieAktywny']) . "',"; // product_active
		$query .= "'" . $vacikVALUE . "',"; // vat_value
		$query .= "'" . $vacikID . "',"; // vat_value
		$query .= "'" . $vacikNAME . "',"; // vat_value
		$query .= "NOW()" . ","; //date_entered
		$query .= "NOW()" . ","; //date_modified
		$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //modified_user_id
		$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //assigned_user_id
		$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //created_by
		$query .= "'" . $jm . "'";
		$query .= ");";
		mysql_query($query);
	}
}
echo '*** Zaktualizowano ' . $update . PHP_EOL;
echo '*** Dodano ' . $insert . PHP_EOL;
echo 'Zakonczono dodawanie towarow' . PHP_EOL;


echo 'Dodawanie faktur sprzedazy' . PHP_EOL;
$query = "SELECT * FROM [CDN_MATINSTAL].[CDN].[TraNag] WHERE TrN_DDfId = 1";

$stmt = mssql_query($query);
if ($stmt === false) {
	echo "Error in query preparation/execution.\n";
	die(print_r(mssql_errors(), true));
}

$query = 'SELECT id FROM ecminvoiceouts';
$return = mysql_query($query);
$existEcmInvoiceOuts = array();
while ($rr = mysql_fetch_assoc($return)) {
	$existEcmInvoiceOuts[] = $rr['id'];
}
$insert = 0;
$update = 0;
$insertItems = 0;
$updateItems = 0;
$jmlist = array(
	1 => 'usł.',
	2 => 't',
	3 => 'szt',
	4 => 'kpl.',
	5 => 'kg',
	6 => 'litr',
	7 => 'm',
	8 => 'godz',
	9 => 'km.',
);

while ($row = mssql_fetch_array($stmt)) {
	if (in_array($row['TrN_TrNID'], $existEcmInvoiceOuts)) {
		$update++;
		$typdokumentu = '';
		if ($row['TrN_DDfId'] == 1) {
			$typdokumentu = 'normal';
		} else {
			$typdokumentu = 'correct';
		}
		$podUlica = '';
		$podUlica .= trim(Encoding::toUTF8($row['TrN_PodUlica'])) . ' ';
		$podUlica .= trim(Encoding::toUTF8($row['TrN_PodNrDomu'])) . ' ';
		$podUlica .= trim(Encoding::toUTF8($row['TrN_PodNrLokalu']));
		$podUlica = trim($podUlica);

		$OdpUlica = '';
		$OdpUlica .= trim(Encoding::toUTF8($row['TrN_OdbUlica'])) . ' ';
		$OdpUlica .= trim(Encoding::toUTF8($row['TrN_OdbNrDomu'])) . ' ';
		$OdpUlica .= trim(Encoding::toUTF8($row['TrN_OdbNrLokalu'])) . ' ';
		$OdpUlica = trim($OdpUlica);

		$formaplatnosci = '';
		if ($row['TrN_FPlId'] != '') {
			$q = "SELECT name FROM ecmpaymentconditions WHERE id = '" . $row['TrN_FPlId'] . "';";
			$formaplatnosci = mysql_fetch_assoc(mysql_query($q));
			$formaplatnosci = $formaplatnosci['name'];
		}

		$magazynNazwa = '';
		if ($row['TrN_FPlId'] != '') {
			$q = "Select name from ecmstocks where id = " . $row['TrN_MagZrdId'] . ";";
			$magazynNazwa = mysql_fetch_assoc(mysql_query($q));
			$magazynNazwa = $magazynNazwa['name'];
		}

		$anulowana = '0';
		if ($row['TrN_Anulowany'] != '') {
			if ($row['TrN_Anulowany'] == '-2147483647') {
				$anulowana = 1;
			}
		}
		try {
			$tz = new DateTimeZone("Europe/Warsaw");
			$dateTermin = new DateTime($row['TrN_Termin'], $tz);
			$dateDataOpe = new DateTime($row['TrN_DataOpe'], $tz);
			$dateDataDok = new DateTime($row['TrN_DataDok'], $tz);
		} catch (Exception $e) {
			echo $e->getMessage();
			exit(1);
		}

		$query = "UPDATE ecminvoiceouts SET ";
		$query.= " name='" . Encoding::toUTF8($row['TrN_NumerPelny']) . "',";
		$query.= " date_modified=NOW(),";
		$query.= " number='" . Encoding::toUTF8($row['TrN_NumerPelny']) . "',";
		$query.= " document_no='" . Encoding::toUTF8($row['TrN_NumerPelny']) . "',";
		$query.= " parent_name='" . trim(trim(Encoding::toUTF8($row['TrN_PodNazwa1'])) . ' ' . trim(Encoding::toUTF8($row['TrN_PodNazwa2'])) . ' ' . trim(Encoding::toUTF8($row['TrN_PodNazwa3']))) . "',";
		$query.= " parent_id='" . Encoding::toUTF8($row['TrN_PodID']) . "',";
		$query.= " type='" . $typdokumentu . "',";
		$query.= " register_date='" . $dateDataDok->format('Y-m-d') . "',";
		$query.= " total_netto='" . $row['TrN_RazemNetto'] . "',";
		$query.= " total_brutto='" . $row['TrN_RazemBrutto'] . "',";
		$query.= " parent_address_street='" . $podUlica . "',";
		$query.= " parent_address_city='" . Encoding::toUTF8($row['TrN_PodMiasto']) . "',";
		$query.= " parent_address_postalcode='" . Encoding::toUTF8($row['TrN_PodKodPocztowy']) . "',";
		$query.= " parent_address_country='" . Encoding::toUTF8($row['TrN_PodKraj']) . "',";
		$query.= " parent_nip_unformated='" . Encoding::toUTF8($row['TrN_PodNipE']) . "',";
		$query.= " parent_nip='" . str_replace('-', '', Encoding::toUTF8($row['TrN_PodNipE'])) . "',";
		$query.= " ecmpaymentcondition_id='" . Encoding::toUTF8($row['TrN_FPlId']) . "',";
		$query.= " ecmpaymentcondition_text='" . $formaplatnosci . "',";
		$query.= " sell_date='" . $dateDataOpe->format('Y-m-d') . "',";
		$query.= " stock_id='" . Encoding::toUTF8($row['TrN_MagZrdId']) . "',";
		$query.= " stock_name='" . $magazynNazwa . "',";
		$query.= " parent_shipping_address_name='" . trim(trim(Encoding::toUTF8($row['TrN_OdbNazwa1'])) . ' ' . trim(Encoding::toUTF8($row['TrN_OdbNazwa2'])) . ' ' . trim(Encoding::toUTF8($row['TrN_OdbNazwa3']))) . "',";
		$query.= " parent_shipping_address_street='" . $OdpUlica . "',";
		$query.= " parent_shipping_address_city='" . Encoding::toUTF8($row['TrN_OdbMiasto']) . "',";
		$query.= " parent_shipping_address_postalcode='" . Encoding::toUTF8($row['TrN_OdbKodPocztowy']) . "',";
		$query.= " parent_shipping_address_country='" . Encoding::toUTF8($row['TrN_OdbKraj']) . "',";
		$query.= " payment_date='" . $dateTermin->format('Y-m-d') . "',";
		$query.= " canceled='" . $anulowana . "',";
		$query.= " total_vat='" . Encoding::toUTF8($row['TrN_RazemVAT']) . "',";
		$query.= " discount='" . Encoding::toUTF8($row['TrN_Rabat']) . "' ";
		$query .= " WHERE id = '" . $row['TrN_TrNID'] . "'";
		mysql_query($query);

		$query2 = "SELECT * FROM [CDN_MATINSTAL].[CDN].[TraElem] where TrE_TrNID = " . $row['TrN_TrNID'];
		$stmt2 = mssql_query($query2);
		if ($stmt2 === false) {
			echo "Error in query preparation/execution.\n";
			die(print_r(mssql_errors(), true));
		}
		$query = "SELECT id FROM ecminvoiceoutitems WHERE ecminvoiceout_id = '" . $row['TrN_TrNID'] . "'";
		$return = mysql_query($query);
		$existEcmInvoiceOutsItems = array();
		while ($rr = mysql_fetch_assoc($return)) {
			$existEcmInvoiceOutsItems[] = $rr['id'];
		}
		while ($row2 = mssql_fetch_array($stmt2)) {
			if (in_array($row2['TrE_TrEID'], $existEcmInvoiceOutsItems)) {
				$updateItems++;
				$jm = null;
				$jmtmp = Encoding::toUTF8($row2['TrE_Jm']);
				switch ($jmtmp) {
					case 'usі.':
						$jm = 1;
						break;
					case 't':
						$jm = 2;
						break;
					case 'szt':
						$jm = 3;
						break;
					case 'kpl.':
						$jm = 4;
						break;
					case 'kg':
						$jm = 5;
						break;
					case 'litr':
						$jm = 6;
						break;
					case 'm':
						$jm = 7;
						break;
					case 'godz':
						$jm = 8;
						break;
					case 'km.':
						$jm = 9;
						break;
				}
				$vacikID = $vatList[$row2['TrE_Stawka'] . '.00']['id'];
				$vacikNAME = $vatList[$row2['TrE_Stawka'] . '.00']['name'];
				$vacikVALUE = $vatList[$row2['TrE_Stawka'] . '.00']['value'];
				$pricenetto = $row2['TrE_CenaW'];
				$pricebrutto = round($pricenetto * $vacikVALUE / 100 + $pricenetto, 2);
				$totalvat = round($pricenetto * $vacikVALUE / 100, 2);

				$query = "UPDATE ecminvoiceoutitems SET ";
				$query.= "	date_modified=NOW(),";
				$query.= "	ecminvoiceout_id='" . $row2['TrE_TrNId'] . "',";
				$query.= "	ecmproduct_id='" . $row2['TrE_TwrId'] . "',";
				$query.= "	position='" . $row2['TrE_Lp'] . "',";
				$query.= "	code='" . Encoding::toUTF8($row2['TrE_TwrKod']) . "',";
				$query.= "	name='" . Encoding::toUTF8($row2['TrE_TwrNazwa']) . "',";
				$query.= "	quantity='" . round($row2['TrE_Ilosc']) . "',";
				$query.= "	price_start='" . $row2['TrE_Cena0'] . "',";
				$query.= "	price_netto='" . $row2['TrE_CenaW'] . "',";
				$query.= "	discount='" . $row2['TrE_Rabat'] . "',";
				$query.= "	total_netto='" . $row2['TrE_WartoscNetto'] . "',";
				$query.= "	total_brutto='" . $row2['TrE_WartoscBrutto'] . "',";
				$query.= "	dd_unit_id='" . $jm . "',";
				$query.= "	dd_unit_name='" . $jmlist[$jm] . "',";
				$query.= "	ecmvat_id='" . $vacikID . "',";
				$query.= "	ecmvat_name='" . $vacikNAME . "',";
				$query.= "	ecmvat_value='" . $vacikVALUE . "',";
				$query.= "	total_vat='" . $totalvat . "',";
				$query.= "	price_brutto='" . $pricebrutto . "'";
				$query .= " WHERE id = '" . $row2['TrE_TrEID'] . "'";
				$rows = mysql_query($query);
			} else {
				$insertItems++;
				$jm = null;
				$jmtmp = Encoding::toUTF8($row2['TrE_Jm']);
				switch ($jmtmp) {
					case 'usі.':
						$jm = 1;
						break;
					case 't':
						$jm = 2;
						break;
					case 'szt':
						$jm = 3;
						break;
					case 'kpl.':
						$jm = 4;
						break;
					case 'kg':
						$jm = 5;
						break;
					case 'litr':
						$jm = 6;
						break;
					case 'm':
						$jm = 7;
						break;
					case 'godz':
						$jm = 8;
						break;
					case 'km.':
						$jm = 9;
						break;
				}
				$vacikID = $vatList[$row2['TrE_Stawka'] . '.00']['id'];
				$vacikNAME = $vatList[$row2['TrE_Stawka'] . '.00']['name'];
				$vacikVALUE = $vatList[$row2['TrE_Stawka'] . '.00']['value'];
				$pricenetto = $row2['TrE_CenaW'];
				$pricebrutto = round($pricenetto * $vacikVALUE / 100 + $pricenetto, 2);
				$totalvat = round($pricenetto * $vacikVALUE / 100, 2);

				$query = "INSERT ecminvoiceoutitems (";
				$query.= "	id,";
				$query.= "	date_entered,";
				$query.= "	date_modified,";
				$query.= "	modified_user_id,";
				$query.= "	assigned_user_id,";
				$query.= "	created_by,";
				$query.= "	deleted,";
				$query.= "	ecminvoiceout_id,";
				$query.= "	ecmproduct_id,";
				$query.= "	position,";
				$query.= "	code,";
				$query.= "	name,";
				$query.= "	quantity,";
				$query.= "	price_start,";
				$query.= "	price_netto,";
				$query.= "	discount,";
				$query.= "	total_netto,";
				$query.= "	total_brutto,";
				$query.= "	dd_unit_id,";
				$query.= "	dd_unit_name,";
				$query.= "	ecmvat_id,";
				$query.= "	ecmvat_name,";
				$query.= "	ecmvat_value,";
				$query.= "	total_vat,";
				$query.= "	price_brutto
				) VALUES (";
				$query .= "'" . $row2['TrE_TrEID'] . "',"; // id
				$query .= "NOW()" . ","; //date_entered
				$query .= "NOW()" . ","; //date_modified
				$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //modified_user_id
				$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //assigned_user_id
				$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //created_by
				$query .= "0,"; // deleted
				$query .= "'" . $row2['TrE_TrNId'] . "',"; // ecminvoiceout_id
				$query .= "'" . $row2['TrE_TwrId'] . "',"; // ecmproduct_id
				$query .= "'" . $row2['TrE_Lp'] . "',"; // position
				$query .= "'" . Encoding::toUTF8($row2['TrE_TwrKod']) . "',"; // code
				$query .= "'" . Encoding::toUTF8($row2['TrE_TwrNazwa']) . "',"; // name
				$query .= "'" . round($row2['TrE_Ilosc']) . "',"; // quantity
				$query .= "'" . $row2['TrE_Cena0'] . "',"; // price_start
				$query .= "'" . $row2['TrE_CenaW'] . "',"; // price_netto
				$query .= "'" . $row2['TrE_Rabat'] . "',"; // discount
				$query .= "'" . $row2['TrE_WartoscNetto'] . "',"; // total_netto
				$query .= "'" . $row2['TrE_WartoscBrutto'] . "',"; // total_brutto
				$query .= "'" . $jm . "',"; // dd_unit_id
				$query .= "'" . $jmlist[$jm] . "',"; // dd_unit_name
				$query .= "'" . $vacikID . "',"; // ecmvat_id
				$query .= "'" . $vacikNAME . "',"; // ecmvat_name
				$query .= "'" . $vacikVALUE . "',"; // ecmvat_value
				$query .= "'" . $totalvat . "',"; // total_vat
				$query .= "'" . $pricebrutto . "'"; // price_brutto
				$query .= ");";
				mysql_query($query);
			}
		}
	} else {
		$insert++;
		$typdokumentu = '';
		if ($row['TrN_DDfId'] == 1) {
			$typdokumentu = 'normal';
		} else {
			$typdokumentu = 'correct';
		}
		$podUlica = '';
		$podUlica .= trim(Encoding::toUTF8($row['TrN_PodUlica'])) . ' ';
		$podUlica .= trim(Encoding::toUTF8($row['TrN_PodNrDomu'])) . ' ';
		$podUlica .= trim(Encoding::toUTF8($row['TrN_PodNrLokalu']));
		$podUlica = trim($podUlica);

		$OdpUlica = '';
		$OdpUlica .= trim(Encoding::toUTF8($row['TrN_OdbUlica'])) . ' ';
		$OdpUlica .= trim(Encoding::toUTF8($row['TrN_OdbNrDomu'])) . ' ';
		$OdpUlica .= trim(Encoding::toUTF8($row['TrN_OdbNrLokalu'])) . ' ';
		$OdpUlica = trim($OdpUlica);

		$formaplatnosci = '';
		if ($row['TrN_FPlId'] != '') {
			$q = "SELECT name FROM ecmpaymentconditions WHERE id = '" . $row['TrN_FPlId'] . "';";
			$formaplatnosci = mysql_fetch_assoc(mysql_query($q));
			$formaplatnosci = $formaplatnosci['name'];
		}

		$magazynNazwa = '';
		if ($row['TrN_FPlId'] != '') {
			$q = "Select name from ecmstocks where id = " . $row['TrN_MagZrdId'] . ";";
			$magazynNazwa = mysql_fetch_assoc(mysql_query($q));
			$magazynNazwa = $magazynNazwa['name'];
		}

		$anulowana = '0';
		if ($row['TrN_Anulowany'] != '') {
			if ($row['TrN_Anulowany'] == '-2147483647') {
				$anulowana = 1;
			}
		}
		try {
			$tz = new DateTimeZone("Europe/Warsaw");
			$dateTermin = new DateTime($row['TrN_Termin'], $tz);
			$dateDataOpe = new DateTime($row['TrN_DataOpe'], $tz);
			$dateDataDok = new DateTime($row['TrN_DataDok'], $tz);
		} catch (Exception $e) {
			echo $e->getMessage();
			exit(1);
		}

		$query = "INSERT ecminvoiceouts (
				id,
				name,
				deleted,
				date_entered,
				date_modified,
				modified_user_id,
				assigned_user_id,
				created_by,
				number,
				document_no,
				parent_type,
				parent_name,
				parent_id,
				type,
				register_date,
				total_netto,
				total_brutto,
				ecmlanguage,
				parent_address_street,
				parent_address_city,
				parent_address_postalcode,
				parent_address_country,
				parent_nip_unformated,
				parent_nip,
				ecmpaymentcondition_id,
				ecmpaymentcondition_text,
				currency_id,
				sell_date,
				stock_id,
				stock_name,
				pdf_type,
				parent_shipping_address_name,
				parent_shipping_address_street,
				parent_shipping_address_city,
				parent_shipping_address_postalcode,
				parent_shipping_address_country,
				payment_date,
				canceled,
				total_vat,
				discount
				) VALUES (";
		$query .= "'" . $row['TrN_TrNID'] . "',"; // id
		$query .= "'" . Encoding::toUTF8($row['TrN_NumerPelny']) . "',"; // name
		$query .= "0,"; // deleted
		$query .= "NOW()" . ","; //date_entered
		$query .= "NOW()" . ","; //date_modified
		$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //modified_user_id
		$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //assigned_user_id
		$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //created_by
		$query .= "'" . Encoding::toUTF8($row['TrN_NumerPelny']) . "',"; // number
		$query .= "'" . Encoding::toUTF8($row['TrN_NumerPelny']) . "',"; // document_no
		$query .= "'Accounts',"; // parent_type
		$query .= "'" . trim(trim(Encoding::toUTF8($row['TrN_PodNazwa1'])) . ' ' . trim(Encoding::toUTF8($row['TrN_PodNazwa2'])) . ' ' . trim(Encoding::toUTF8($row['TrN_PodNazwa3']))) . "',"; // parent_name
		$query .= "'" . Encoding::toUTF8($row['TrN_PodID']) . "',"; // parent_name
		$query .= "'" . $typdokumentu . "',"; // type
		$query .= "'" . $dateDataDok->format('Y-m-d') . "',"; // register_date
		$query .= "'" . $row['TrN_RazemNetto'] . "',"; // total_netto
		$query .= "'" . $row['TrN_RazemBrutto'] . "',"; // total_brutto
		$query .= "'pl_pl',"; //ecmlanguage
		$query .= "'" . $podUlica . "',"; // parent_address_street
		$query .= "'" . Encoding::toUTF8($row['TrN_PodMiasto']) . "',"; // parent_address_city
		$query .= "'" . Encoding::toUTF8($row['TrN_PodKodPocztowy']) . "',"; // parent_address_postalcode
		$query .= "'" . Encoding::toUTF8($row['TrN_PodKraj']) . "',"; // parent_address_country
		$query .= "'" . Encoding::toUTF8($row['TrN_PodNipE']) . "',"; // parent_nip_unformated
		$query .= "'" . str_replace('-', '', Encoding::toUTF8($row['TrN_PodNipE'])) . "',"; // parent_nip
		$query .= "'" . Encoding::toUTF8($row['TrN_FPlId']) . "',"; // ecmpaymentcondition_id
		$query .= "'" . $formaplatnosci . "',"; // ecmpaymentcondition_text
		$query .= "'PLN',"; //currency_id
		$query .= "'" . $dateDataOpe->format('Y-m-d') . "',"; // sell_date
		$query .= "'" . Encoding::toUTF8($row['TrN_MagZrdId']) . "',"; // stock_id
		$query .= "'" . $magazynNazwa . "',"; // stock_name
		$query .= "'K',"; //pdf_type
		$query .= "'" . Encoding::toUTF8($row['TrN_OdbNazwa1']) . ' ' . Encoding::toUTF8($row['TrN_OdbNazwa2']) . ' ' . Encoding::toUTF8($row['TrN_OdbNazwa3']) . "',"; // parent_shipping_address_name
		$query .= "'" . $OdpUlica . "',"; // parent_shipping_address_street
		$query .= "'" . Encoding::toUTF8($row['TrN_OdbMiasto']) . "',"; // parent_shipping_address_city
		$query .= "'" . Encoding::toUTF8($row['TrN_OdbKodPocztowy']) . "',"; // parent_address_postalcode
		$query .= "'" . Encoding::toUTF8($row['TrN_OdbKraj']) . "',"; // parent_address_country
		$query .= "'" . $dateTermin->format('Y-m-d') . "',"; // payment_date
		$query .= "'" . $anulowana . "',"; // canceled
		$query .= "'" . Encoding::toUTF8($row['TrN_RazemVAT']) . "',"; // total_vat
		$query .= "'" . Encoding::toUTF8($row['TrN_Rabat']) . "'"; // discount
		$query .= ");";
		$rows = mysql_query($query);


		$query2 = "SELECT * FROM [CDN_MATINSTAL].[CDN].[TraElem] where TrE_TrNID = " . $row['TrN_TrNID'];
		$stmt2 = mssql_query($query2);
		if ($stmt2 === false) {
			echo "Error in query preparation/execution.\n";
			die(print_r(mssql_errors(), true));
		}
		while ($row2 = mssql_fetch_array($stmt2)) {
			$insertItems++;
			$jm = null;
			$jmtmp = Encoding::toUTF8($row2['TrE_Jm']);
			switch ($jmtmp) {
				case 'usі.':
					$jm = 1;
					break;
				case 't':
					$jm = 2;
					break;
				case 'szt':
					$jm = 3;
					break;
				case 'kpl.':
					$jm = 4;
					break;
				case 'kg':
					$jm = 5;
					break;
				case 'litr':
					$jm = 6;
					break;
				case 'm':
					$jm = 7;
					break;
				case 'godz':
					$jm = 8;
					break;
				case 'km.':
					$jm = 9;
					break;
			}
			$vacikID = $vatList[$row2['TrE_Stawka'] . '.00']['id'];
			$vacikNAME = $vatList[$row2['TrE_Stawka'] . '.00']['name'];
			$vacikVALUE = $vatList[$row2['TrE_Stawka'] . '.00']['value'];
			$pricenetto = $row2['TrE_CenaW'];
			$pricebrutto = round($pricenetto * $vacikVALUE / 100 + $pricenetto, 2);
			$totalvat = round($pricenetto * $vacikVALUE / 100, 2);

			$query = "INSERT ecminvoiceoutitems (";
			$query.= "	id,";
			$query.= "	date_entered,";
			$query.= "	date_modified,";
			$query.= "	modified_user_id,";
			$query.= "	assigned_user_id,";
			$query.= "	created_by,";
			$query.= "	deleted,";
			$query.= "	ecminvoiceout_id,";
			$query.= "	ecmproduct_id,";
			$query.= "	position,";
			$query.= "	code,";
			$query.= "	name,";
			$query.= "	quantity,";
			$query.= "	price_start,";
			$query.= "	price_netto,";
			$query.= "	discount,";
			$query.= "	total_netto,";
			$query.= "	total_brutto,";
			$query.= "	dd_unit_id,";
			$query.= "	dd_unit_name,";
			$query.= "	ecmvat_id,";
			$query.= "	ecmvat_name,";
			$query.= "	ecmvat_value,";
			$query.= "	total_vat,";
			$query.= "	price_brutto
			) VALUES (";
			$query .= "'" . $row2['TrE_TrEID'] . "',"; // id
			$query .= "NOW()" . ","; //date_entered
			$query .= "NOW()" . ","; //date_modified
			$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //modified_user_id
			$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //assigned_user_id
			$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //created_by
			$query .= "0,"; // deleted
			$query .= "'" . $row2['TrE_TrNId'] . "',"; // ecminvoiceout_id
			$query .= "'" . $row2['TrE_TwrId'] . "',"; // ecmproduct_id
			$query .= "'" . $row2['TrE_Lp'] . "',"; // position
			$query .= "'" . Encoding::toUTF8($row2['TrE_TwrKod']) . "',"; // code
			$query .= "'" . Encoding::toUTF8($row2['TrE_TwrNazwa']) . "',"; // name
			$query .= "'" . round($row2['TrE_Ilosc']) . "',"; // quantity
			$query .= "'" . $row2['TrE_Cena0'] . "',"; // price_start
			$query .= "'" . $row2['TrE_CenaW'] . "',"; // price_netto
			$query .= "'" . $row2['TrE_Rabat'] . "',"; // discount
			$query .= "'" . $row2['TrE_WartoscNetto'] . "',"; // total_netto
			$query .= "'" . $row2['TrE_WartoscBrutto'] . "',"; // total_brutto
			$query .= "'" . $jm . "',"; // dd_unit_id
			$query .= "'" . $jmlist[$jm] . "',"; // dd_unit_name
			$query .= "'" . $vacikID . "',"; // ecmvat_id
			$query .= "'" . $vacikNAME . "',"; // ecmvat_name
			$query .= "'" . $vacikVALUE . "',"; // ecmvat_value
			$query .= "'" . $totalvat . "',"; // total_vat
			$query .= "'" . $pricebrutto . "'"; // price_brutto
			$query .= ");";
			mysql_query($query);
		}
	}
}
echo '*** Zaktualizowano ' . $update . PHP_EOL;
echo '*** Dodano ' . $insert . PHP_EOL;
echo '****** Zaktualizowano wpisów faktur ' . $updateItems . PHP_EOL;
echo '****** Dodano wpisów faktur ' . $insertItems . PHP_EOL;
echo 'Zakonczono dodawanie faktur sprzedazy' . PHP_EOL;


echo 'Dodawanie rozrachunkow' . PHP_EOL;
$query = "SELECT * FROM [CDN_MATINSTAL].[CDN].[TraNag]
    where TrN_Anulowany != -2147483647
    AND TrN_DDfId = 1 AND
    TrN_FPlId != 1;";

$stmt = mssql_query($query);
if ($stmt === false) {
	echo "Error in query preparation/execution.\n";
	//die( print_r( mssql_errors(), true));
}
$query = 'SELECT id FROM ecmtransactions';
$return = mysql_query($query);
$existEcmTransactions = array();
while ($rr = mysql_fetch_assoc($return)) {
	$existEcmTransactions[] = $rr['id'];
}
$insert = 0;
$update = 0;
while ($row = mssql_fetch_array($stmt)) {
	if (in_array($row['TrN_TrNID'], $existEcmTransactions)) {
		$update++;
		$parentName = '';
		if ($row['TrN_PodID'] != '') {
			$q = "Select name from accounts where id = '" . $row['TrN_PodID'] . "';";
			$parentName = mysql_fetch_assoc(mysql_query($q));
			$parentName = $parentName['name'];
		}

		try {
			$tz = new DateTimeZone("Europe/Warsaw");
			$dateTermin = new DateTime($row['TrN_Termin'], $tz);
			$dateDataDok = new DateTime($row['TrN_DataDok'], $tz);
		} catch (Exception $e) {
			echo $e->getMessage();
			exit(1);
		}
		$query = "UPDATE ecmtransactions SET ";
		$query .= "date_modified=NOW(),";
		$query .= "name='" . Encoding::toUTF8($row['TrN_NumerPelny']) . "',";
		$query .= "value='" . $row['TrN_RazemBrutto'] . "',";
		$query .= "parent_name='" . $parentName . "',";
		$query .= "parent_id='" . $row['TrN_PodID'] . "',";
		$query .= "register_date='" . $dateDataDok->format('Y-m-d') . "',";
		$query .= "payment_date='" . $dateTermin->format('Y-m-d') . "',";
		$query .= "record_id='" . $row['TrN_TrNID'] . "',";
		$query .= " WHERE id = '" . $row['TrN_TrNID'] . "'";
		mysql_query($query);
	} else {
		$insert++;
		$parentName = '';
		if ($row['TrN_PodID'] != '') {
			$q = "Select name from accounts where id = '" . $row['TrN_PodID'] . "';";
			$parentName = mysql_fetch_assoc(mysql_query($q));
			$parentName = $parentName['name'];
		}

		try {
			$tz = new DateTimeZone("Europe/Warsaw");
			$dateTermin = new DateTime($row['TrN_Termin'], $tz);
			$dateDataDok = new DateTime($row['TrN_DataDok'], $tz);
		} catch (Exception $e) {
			echo $e->getMessage();
			exit(1);
		}
		$query = "INSERT ecmtransactions (
					id,
					date_entered,
					date_modified,
					modified_user_id,
					assigned_user_id,
					created_by,
					deleted,
					name,
					value,
					parent_name,
					parent_id,
					type,
					register_date,
					payment_date,
					currency_id,
					record_id,
					record_type
					) VALUES (";
		$query .= "'" . $row['TrN_TrNID'] . "',"; // id
		$query .= "NOW()" . ","; //date_entered
		$query .= "NOW()" . ","; //date_modified
		$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //modified_user_id
		$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //assigned_user_id
		$query .= "'243170e7-70c9-99c8-6ef3-542a4ff7e733',"; //created_by
		$query .= "0,"; // deleted
		$query .= "'" . Encoding::toUTF8($row['TrN_NumerPelny']) . "',"; // name
		$query .= "'" . $row['TrN_RazemBrutto'] . "'" . ","; //value
		$query .= "'" . $parentName . "'" . ","; //parent_name
		$query .= "'" . $row['TrN_PodID'] . "',"; //parent_id
		$query .= "0,"; // type
		$query .= "'" . $dateDataDok->format('Y-m-d') . "',"; //register_date
		$query .= "'" . $dateTermin->format('Y-m-d') . "',"; //payment_date
		$query .= "'PLN',"; // currency_id
		$query .= "'" . $row['TrN_TrNID'] . "',"; // id
		$query .= "'EcmInvoiceOuts'"; // currency_id
		$query .= ");";
		mysql_query($query);
	}
}
echo '*** Zaktualizowano ' . $update . PHP_EOL;
echo '*** Dodano ' . $insert . PHP_EOL;
echo 'Zakonczono dodawanie rozrachunkow' . PHP_EOL;

mysql_close($myconn);
mssql_close($msconn);
echo 'Zakonczono połączenie' . PHP_EOL;