se/SE/se-lib/Route/ZaliczkiNajemcow.php

<?php

Lib::loadClass('RouteBase');
Lib::loadClass('TypespecialVariable');
Lib::loadClass('Router');

class Route_ZaliczkiNajemcow extends RouteBase {

	public function handleAuth() {
		if (!User::logged()) {
			throw new HttpException('Unauthorized', 401);
		}
	}

	public function defaultAction() {
		SE_Layout::gora();
		//SE_Layout::menu();
		$this->menu();
		SE_Layout::dol();
	}

	public function menu($selectedYear = null, $selectedNajemca = null, $selectedCostGroup = null) {
		$typeSpecialNajemca  = TypespecialVariable::getInstance(-1, '__ZALICZKI_NAJEMCOW__NAJEMCA');
		$typeSpecialCostGroup  = TypespecialVariable::getInstance(-1, '__USER_GROUPS');
		$year = ($selectedYear)? $selectedYear : date("Y");
		?>
<div class="jumbotron">
  <div class="container">
		<h3>Rozliczenia najemców</h3>
		<form class="form-inline" method="POST">
			<input type="hidden" name="_task" value="zaliczki" />
			<label for="year">Wybierz rok:</label>
			<div class="input-group date" id="fldYear">
				<input type="text" name="year" class="form-control" value="" />
				<span class="input-group-addon"><span class="glyphicon glyphicon-time"></span></span>
			</div>
			<br>
			<br>

			<label for="telbox">Wybierz najemcę:</label>
			<?php if ($typeSpecialNajemca) : ?>
				<?php
					$fName = 'najemca';
					$fldParams = array();
					$fldParams['allowCreate'] = false;
					$fldParams['ajaxDataUrlBase'] = "index.php?_route=ZaliczkiNajemcow&_task=typespecial&fld={$fName}";
					//$fldParams['ajaxDataUrlBase'] .= "&DBG_TS=3";
					echo $typeSpecialNajemca->showFormItem($tblID = -1, $fName, $selValue = $selectedNajemca, $fldParams);
				?>
			<?php endif; ?>
			<br>

			<label for="group">Wybierz grupę kosztów:</label>
			<?php if ($typeSpecialCostGroup) : ?>
				<?php
					$fName = 'cost_group';
					// TODO: default group [6388] DZIAL ADMINISTRACJI
					$fldParams = array();
					$fldParams['allowCreate'] = false;
					$fldParams['ajaxDataUrlBase'] = "index.php?_route=ZaliczkiNajemcow&_task=typespecial&fld={$fName}";
					//$fldParams['ajaxDataUrlBase'] .= "&DBG_TS=3";
					if (!empty($selectedCostGroup)) {
						$tsValue = '';
						$tsValues = $typeSpecialCostGroup->getValuesWithExports($selectedCostGroup);
						if (!empty($tsValues)) {
							foreach ($tsValues as $tsItem) {
								if ($selectedCostGroup == $tsItem->id) {
									$tsValue = $tsItem->param_out;
								}
							}
						}
						$fldParams['typespecialValue'] = $tsValue;
					}
					echo $typeSpecialCostGroup->showFormItem($tblID = -1, $fName, $selValue = $selectedCostGroup, $fldParams);
				?>
			<?php endif; ?>
			<br>
			<button type="submit" id="fldGenerateBtn" class="btn btn-primary" autocomplete="off">
				Zaliczki
			</button>
		</form>
<!--
		<div style="text-align:right">
			Edytuj
			<a href="index.php?_route=Budget&_task=plan&year=<?php echo $year; ?>"
				 class="btn btn-xs btn-default"
				 title="Plan budżetu (projects_budget_year_month)">plan budżetu</a>
			na rok <?php echo $year; ?>
		</div>
-->
	</div>
</div>
<script type="text/javascript">
jQuery(document).ready(function () {
  jQuery('#fldGenerateBtn').on('click', function () {
    jQuery(this).text(jQuery(this).text() + '...').attr('disabled', 'disabled');
		jQuery(this).parent().submit();
  })

	jQuery("#fldYear").datetimepicker({
		format: "YYYY",
		defaultDate: new Date(<?php echo $year; ?>, <?php echo intval(date("m")); ?>, 1),
		// minDate: new Date(2014, 11, 1),
		// maxDate: "<?php echo date("Y"); ?>"
	});
});
</script>
<?php
	}

	public function typespecialAction() {
		header("Content-type: application/json");

		$fld = V::get('fld', '', $_GET);
		switch ($fld) {
			case 'najemca': {
					$typeSpecialNajemca = TypespecialVariable::getInstance(-1, '__ZALICZKI_NAJEMCOW__NAJEMCA');

					$query = V::get('q', '', $_REQUEST);
					$rawRows = null;
					$rows = $typeSpecialNajemca->getValuesWithExports($query);
					DBG::_('DBG', '>0', "rows({$query})", $rows, __CLASS__, __FUNCTION__, __LINE__);
					foreach ($rows as $kID => $vItem) {
						$itemJson = new stdClass();
						$itemJson->id = $vItem->id;
						$itemJson->name = $vItem->param_out;
						if (!empty($vItem->exports)) {
							$itemJson->exports = $vItem->exports;
						}
						$jsonData[] = $itemJson;
					}
					echo json_encode($jsonData);
				}
				break;
			case 'cost_group': {
					$typeSpecialCostGroup = TypespecialVariable::getInstance(-1, '__USER_GROUPS');

					$query = V::get('q', '', $_REQUEST);
					$rawRows = null;
					$rows = $typeSpecialCostGroup->getValuesWithExports($query);
					DBG::_('DBG', '>0', "rows({$query})", $rows, __CLASS__, __FUNCTION__, __LINE__);
					foreach ($rows as $kID => $vItem) {
						$itemJson = new stdClass();
						$itemJson->id = $vItem->id;
						$itemJson->name = $vItem->param_out;
						if (!empty($vItem->exports)) {
							$itemJson->exports = $vItem->exports;
						}
						$jsonData[] = $itemJson;
					}
					echo json_encode($jsonData);
				}
				break;
		}
	}

	public function zaliczkaAction() {
		SE_Layout::gora();
		//SE_Layout::menu();
		try {
			$id = V::get('nrZaliczki', 0, $_REQUEST, 'int');
			if (!$id) throw new Exception("Wrong param id");
			$zaliczka = $this->findZaliczkaById($id);
			$this->validateRowZaliczka($zaliczka);
			$lastCountCostDate = $this->getLastCountMonthCost($zaliczka);
			if (null === $lastCountCostDate) {
				$zaliczka = $this->recountMonthCost($zaliczka, $zaliczka['A_LAST_COST_COUNT_ID_GROUP']);
			}
			$this->zaliczka($zaliczka);
		} catch (Exception $e) {
			SE_Layout::alert('danger', "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage());
		}
		SE_Layout::dol();
	}

	public function zaliczkiAction() {
		$selectedYear = V::get('year', '', $_REQUEST);
		$selectedNajemca = V::get('najemca', '', $_REQUEST);
		$selectedCostGroup = V::get('cost_group', '', $_REQUEST);
		SE_Layout::gora();
		//SE_Layout::menu();
		try {
			$this->menu($selectedYear, $selectedNajemca, $selectedCostGroup);
			if (!empty($selectedYear)) {
				if (empty($selectedCostGroup)) throw new Exception("Wybierz grupę");
				$groups = array($selectedCostGroup);

				$zaliczka = $this->findZaliczkaByNameYear($selectedNajemca, $selectedYear);
				$this->validateRowZaliczka($zaliczka);
				$lastCountCostDate = $this->getLastCountMonthCost($zaliczka);
				if (null === $lastCountCostDate || $selectedCostGroup != $zaliczka['A_LAST_COST_COUNT_ID_GROUP']) {
					$zaliczka = $this->recountMonthCost($zaliczka, $selectedCostGroup);
				}
				$this->zaliczka($zaliczka);
			}
		} catch (Exception $e) {
			SE_Layout::alert('danger', "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage());
		}
		SE_Layout::dol();
	}

	public function zaliczka($zaliczka) {//$selectedYear = null, $selectedNajemca = null, $selectedCostGroup = null) {
		$incomeByMonth = array();
		$localCostByMonth = array();
		for ($month = 1; $month <= 12; $month++) {
			$fldMonth = sprintf("%02d", $month);
			$incomeByMonth[$month] = $zaliczka["m{$fldMonth}"];
			$localCostByMonth[$month] = $zaliczka["c{$fldMonth}"];
		}
		$localCostTotal = 0; for($month = 1; $month <= 12; $month++) $localCostTotal += $localCostByMonth[$month];

		$incomeTotal = 0; for($month = 1; $month <= 12; $month++) $incomeTotal += $incomeByMonth[$month];
		$diff = array(); for($month = 1; $month <= 12; $month++) $diff[$month] = $localCostByMonth[$month] - $incomeByMonth[$month];
		$sumTotalDiff = 0; for($month = 1; $month <= 12; $month++) $sumTotalDiff += $diff[$month];
		$payTotal = -1 * ($incomeTotal + $localCostTotal);
		?>
<div class="container">
	<div class="row">
		<div class="col-md-3" style="text-align:right">Najemca</div>
		<div class="col-md-3"><?php echo $zaliczka['NAJEMCA']; ?></div>
		<div class="col-md-3" style="text-align:right">Powierzchnia całkowita do wynajęcia [m<sup>2</sup>]</div>
		<div class="col-md-3"><?php echo $zaliczka['POW_CALK']; ?></div>
	</div>
	<div class="row">
		<div class="col-md-3" style="text-align:right">Nr lokalu</div>
		<div class="col-md-3"><?php echo $zaliczka['NR_LOKALU']; ?></div>
		<div class="col-md-3" style="text-align:right">Powierzchnia lokalu przyjęta do obliczeń [m<sup>2</sup>]</div>
		<div class="col-md-3"><?php echo $zaliczka['POW_LOKALU']; ?></div>
	</div>
	<div class="row">
		<div class="col-md-3" style="text-align:right">Czy bierze udział w opdziale kosztów opłat administracyjnych</div>
		<div class="col-md-3"><?php echo $zaliczka['CZY_ROZLICZENIE']; ?></div>
		<div class="col-md-3" style="text-align:right">Rozliczenie za okres</div>
		<div class="col-md-3"><?php echo $zaliczka['KOSZT_OD']; ?> - <?php echo $zaliczka['KOSZT_DO']; ?></div>
	</div>
	<div class="row">
		<div class="col-md-3" style="text-align:right">Rok</div>
		<div class="col-md-3"><?php echo $zaliczka['ROK']; ?></div>
	</div>
	<h4 style="margin:12px;text-align:center">Załącznik do faktury za rozliczenie opłat serwisowych</h4>
	<table class="table table-bordered">
		<tr>
			<th></th>
			<?php for($month = 1; $month <= 12; $month++) : ?>
				<th><?php echo sprintf("%02d", $month); ?></th>
			<?php endfor; ?>
			<th>suma</th>
			<th>stawka średnia za m<sup>2</sup></th>
		</tr>
		<tr>
			<th style="text-align:right">Wpłacone zaliczki</th>
			<?php for($month = 1; $month <= 12; $month++) : ?>
				<td class="success"><?php echo number_format($incomeByMonth[$month], 2, '.', ','); ?></td>
			<?php endfor; ?>
			<th><?php echo number_format($incomeTotal, 2, '.', ','); ?></th>
			<th><?php echo number_format($incomeTotal / 12 / $zaliczka['POW_LOKALU'], 2, '.', ','); ?></th>
		</tr>
		<tr>
			<th style="text-align:right">Koszty przypadające na lokal</th>
			<?php for($month = 1; $month <= 12; $month++) : ?>
				<td class="success"><?php echo number_format($localCostByMonth[$month], 2, '.', ','); ?></td>
			<?php endfor; ?>
			<th><?php echo number_format($localCostTotal, 2, '.', ','); ?></th>
			<th><?php echo number_format($localCostTotal / 12 / $zaliczka['POW_LOKALU'], 2, '.', ','); ?></th>
		</tr>
		<tr>
			<th style="text-align:right">Różnica</th>
			<?php for($month = 1; $month <= 12; $month++) : ?>
				<td class="info"><?php echo number_format($diff[$month], 2, '.', ','); ?></td>
			<?php endfor; ?>
			<th><?php echo number_format($sumTotalDiff, 2, '.', ','); ?></th>
			<th><?php echo number_format($sumTotalDiff / 12 / $zaliczka['POW_LOKALU'], 2, '.', ','); ?></th>
		</tr>
	</table>
	<div class="row" style="margin-top:60px">
		<div class="col-md-12">
			Kwota netto w PLN do zapłaty za rozliczenie kosztów eksploatacyjnych za rok <?php echo $zaliczka['ROK']; ?> wynosi:
			<br>Razem do zapłaty <?php echo number_format($payTotal, 2, '.', ','); ?> zł
			<br><br>Przygotowane przez: <?php echo User::getFullName(); ?>
		</div>
	</div>
</div>
<?php
	}

	public function recountMonthCostAction() {
		SE_Layout::gora();
		//SE_Layout::menu();
		try {
			$id = V::get('nrZaliczki', 0, $_REQUEST, 'int');
			if (!$id) throw new Exception("Wrong param id");
			$zaliczka = $this->findZaliczkaById($id);
			if (!$zaliczka['A_LAST_COST_COUNT_ID_GROUP']) throw new Exception("Group not set");
			$zaliczka = $this->recountMonthCost($zaliczka, $zaliczka['A_LAST_COST_COUNT_ID_GROUP']);
			DBG::_(true, true, "zaliczka", $zaliczka, __CLASS__, __FUNCTION__, __LINE__);
		} catch (Exception $e) {
			SE_Layout::alert('danger', "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage());
		}
		SE_Layout::dol();
	}

	public function recountMonthCost($zaliczka, $idGroup) {
		$idGroup = (int)$idGroup;
		if (!$idGroup) throw new Exception("Podano niepoprawną grupę");
		if ('TAK' != $zaliczka['CZY_ROZLICZENIE']) {
			return $zaliczka;
		}
		$budget = Router::getRoute('Budget');
		$groups = array($idGroup);
		//$budget->fetchDataByYear($selectedYear, $groups);
		{
			$budget->_fetchCostsByYear($zaliczka['ROK']);
			//$budget->_fetchPlanByYear($year);
			$budget->_fetchProjectInfo();
			$budget->_buildProjectTree();
			$budget->_reacountCostsFromKoresp();
			$budget->_filterProjectsByGroups($groups);
		}
		{
			$sumCostByMonth = array(); for ($month = 1; $month <= 12; $month++) $sumCostByMonth[$month] = 0;
			foreach ($budget->_projectInfo as $idProject => $projectInfo) {
				if (!$projectInfo->hasAccess) continue;
				if ($projectInfo->filteredByGroups) continue;
				for ($month = 1; $month <= 12; $month++) {
					$cost = $budget->getCost($idProject, $month);
					if (!$cost) continue;
					$sumCostByMonth[$month] += $cost->COST_SELF;
				}
			}
		}
		DBG::_('DBG', '>1', "sumCostByMonth", $sumCostByMonth, __CLASS__, __FUNCTION__, __LINE__);
		{// only months between KOSZT_OD and KOSZT_DO
			$strMonthFrom = substr($zaliczka['KOSZT_OD'], 0, 7);
			$strMonthTo   = substr($zaliczka['KOSZT_DO'], 0, 7);
			for ($month = 1; $month <= 12; $month++) {
				$strMonth = "{$zaliczka['ROK']}-" . sprintf("%02d", $month);
				if ($strMonth < $strMonthFrom) $sumCostByMonth[$month] = 0;
				if ($strMonth > $strMonthTo)   $sumCostByMonth[$month] = 0;
			}
		}
		DBG::_('DBG', '>1', "sumCostByMonth", $sumCostByMonth, __CLASS__, __FUNCTION__, __LINE__);
		DBG::_('DBG', '>2', "budget", $budget, __CLASS__, __FUNCTION__, __LINE__);
		$kosztOd = new DateTime($zaliczka['KOSZT_OD']);
		$kosztDo = new DateTime($zaliczka['KOSZT_DO']);
		$daysBilled = 1 + $kosztDo->diff($kosztOd)->format("%a");
		if ($daysBilled < 1) throw new Exception("Niepoprawne wartości w polach KOSZT_OD i KOSZT_DO");
		DBG::_('DBG', '>1', "daysBilled", $daysBilled, __CLASS__, __FUNCTION__, __LINE__);
		DBG::_('DBG', '>1', "zaliczka", $zaliczka, __CLASS__, __FUNCTION__, __LINE__);

		$localCostByMonth = array();
		for ($month = 1; $month <= 12; $month++) {
			$localCostByMonth[$month] = ($zaliczka['POW_LOKALU'] / $zaliczka['POW_CALK']) * $sumCostByMonth[$month] * $daysBilled / 365;
		}

		$zaliczkaPatch = array();
		$zaliczkaPatch['ID'] = $zaliczka['ID'];
		$zaliczkaPatch['A_LAST_COST_COUNT_ID_GROUP'] = $idGroup;
		$zaliczkaPatch['A_LAST_COST_COUNT_DATE'] = 'NOW()';
		for ($month = 1; $month <= 12; $month++) {
			$fldMonth = sprintf("%02d", $month);
			$zaliczkaPatch["c{$fldMonth}"] = $localCostByMonth[$month];
		}
		DBG::_('DBG', '>1', "zaliczkaPatch", $zaliczkaPatch, __CLASS__, __FUNCTION__, __LINE__);

		$tblAcl = User::getAcl()->getObjectAcl('default_db', 'ZALICZKI_NAJEMCOW');
		//DBG::_(true, true, "tblAcl", $tblAcl, __CLASS__, __FUNCTION__, __LINE__);
		$ds = $tblAcl->getDataSource();
		//DBG::_(true, true, "ds", $ds, __CLASS__, __FUNCTION__, __LINE__);
		$ds->updateItem($zaliczkaPatch);

		return $this->findZaliczkaById($zaliczka['ID']);
	}

	public function getLastCountMonthCost($zaliczka) {
		return $zaliczka['A_LAST_COST_COUNT_DATE'];
	}

	public function findZaliczkaByNameYear($najemca, $rok) {
		$pdo = DB::getPDO();
		if (empty($najemca)) throw new Exception("Nie podano najemcy");
		if (empty($rok)) throw new Exception("Nie podano roku");
		if (!is_numeric($rok) || 4 != strlen($rok)) throw new Exception("Podano nieprawidłowy rok");
		$sth = $pdo->prepare("
			select z.*
			from ZALICZKI_NAJEMCOW z
			where z.ROK = :rok
				and z.NAJEMCA = :najemca
		");
		$sth->bindValue(':rok', $rok, PDO::PARAM_STR);
		$sth->bindValue(':najemca', $najemca, PDO::PARAM_STR);
		$sth->execute();
		$zaliczka = $sth->fetch();
		if (!$zaliczka) throw new Exception("Brak danych dla wybrancyh kryteriów");
		return $zaliczka;
	}

	public function findZaliczkaById($id) {
		$pdo = DB::getPDO();
		$id = (int)$id;
		if (!$id) throw new Exception("Podano nieprawidłowy nr rekordu");
		$sth = $pdo->prepare("
			select z.*
			from ZALICZKI_NAJEMCOW z
			where z.ID = :id
		");
		$sth->bindValue(':id', $id, PDO::PARAM_INT);
		$sth->execute();
		$zaliczka = $sth->fetch();
		if (!$zaliczka) throw new Exception("Brak danych dla wybrancyh kryteriów");
		return $zaliczka;
	}

	public function validateRowZaliczka($zaliczka) {
		//DBG::_(true, true, "zaliczka", $zaliczka, __CLASS__, __FUNCTION__, __LINE__);
		// TODO: validate $zaliczka
		if (empty($zaliczka['KOSZT_OD'])) throw new Exception("Brak KOSZT_OD");
		if (empty($zaliczka['KOSZT_DO'])) throw new Exception("Brak KOSZT_DO");
		if ($zaliczka['KOSZT_OD'] > $zaliczka['KOSZT_DO']) throw new Exception("KOSZT_OD nie może być później niż KOSZT_DO");
		if (empty($zaliczka['POW_CALK'])) throw new Exception("Brak POW_CALK");
		if (empty($zaliczka['POW_LOKALU'])) throw new Exception("Brak POW_LOKALU");
	}

	public function reinstallAction() {
		$force = (1 == V::get('_force', '', $_REQUEST));
		try {
			$this->_reinstall($force);
			echo "\n" . '.END';
		} catch (Exception $e) {
			echo "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage();
		}
	}

	public function insertTestDataAction() {
		try {
			$pdo = DB::getPDO();
			$pdo->exec("
				insert into `ZALICZKI_NAJEMCOW` values('1',
					'0',
					'test1',
					'',
					'test1',
					'2015',
					'TAK',
					'21736.91',
					'1735.24',
					'2015-05-12',
					'2015-12-31',
					'0.00',
					'0.00',
					'0.00',
					'0.00',
					'13962.75',
					'21642.26',
					'21642.26',
					'21642.26',
					'21642.26',
					'21642.26',
					'21642.26',
					'21642.26',
					'0.00',
					'0.00',
					'0.00',
					'0.00',
					'0.00',
					'0.00',
					'0.00',
					'0.00',
					'0.00',
					'0.00',
					'0.00',
					'0.00',
					NULL,
					'0',
					'2016-02-22-15:34',
					'plabudda',
					'2016-02-23-11:21',
					'plabudda',
					'WAITING',
					'',
					''
				)
			");
			echo "\n" . '.END';
		} catch (Exception $e) {
			echo "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage();
		}
	}

	public function _reinstall($force = false) {
		$sqlList = array();
		$pdo = DB::getPDO();
		$sqlHistCreate = <<<EOF_SQL_HIST_CREATE
CREATE TABLE IF NOT EXISTS `ZALICZKI_NAJEMCOW_HIST` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ID_USERS2` int(11) NOT NULL,
  `ID_BILLING_USERS` varchar(11) NOT NULL DEFAULT 'N/S;',
  `NAJEMCA` varchar(255) NOT NULL DEFAULT 'N/S;',
  `T_TELBOX_NEIGHBOUR_IN` varchar(32) NOT NULL DEFAULT 'N/S;',
  `NR_LOKALU` varchar(32) NOT NULL DEFAULT 'N/S;',
  `ROK` varchar(5) NOT NULL DEFAULT 'N/S;',
  `CZY_ROZLICZENIE` varchar(10) NOT NULL DEFAULT 'N/S;',
  `POW_CALK` varchar(11) NOT NULL DEFAULT 'N/S;',
  `POW_LOKALU` varchar(11) NOT NULL DEFAULT 'N/S;',
  `KOSZT_OD` varchar(10) NOT NULL DEFAULT 'N/S;',
  `KOSZT_DO` varchar(10) NOT NULL DEFAULT 'N/S;',
	`m01` varchar(20) NOT NULL DEFAULT 'N/S;',
	`m02` varchar(20) NOT NULL DEFAULT 'N/S;',
	`m03` varchar(20) NOT NULL DEFAULT 'N/S;',
	`m04` varchar(20) NOT NULL DEFAULT 'N/S;',
	`m05` varchar(20) NOT NULL DEFAULT 'N/S;',
	`m06` varchar(20) NOT NULL DEFAULT 'N/S;',
	`m07` varchar(20) NOT NULL DEFAULT 'N/S;',
	`m08` varchar(20) NOT NULL DEFAULT 'N/S;',
	`m09` varchar(20) NOT NULL DEFAULT 'N/S;',
	`m10` varchar(20) NOT NULL DEFAULT 'N/S;',
	`m11` varchar(20) NOT NULL DEFAULT 'N/S;',
	`m12` varchar(20) NOT NULL DEFAULT 'N/S;',
	`c01` varchar(20) NOT NULL DEFAULT 'N/S;',
	`c02` varchar(20) NOT NULL DEFAULT 'N/S;',
	`c03` varchar(20) NOT NULL DEFAULT 'N/S;',
	`c04` varchar(20) NOT NULL DEFAULT 'N/S;',
	`c05` varchar(20) NOT NULL DEFAULT 'N/S;',
	`c06` varchar(20) NOT NULL DEFAULT 'N/S;',
	`c07` varchar(20) NOT NULL DEFAULT 'N/S;',
	`c08` varchar(20) NOT NULL DEFAULT 'N/S;',
	`c09` varchar(20) NOT NULL DEFAULT 'N/S;',
	`c10` varchar(20) NOT NULL DEFAULT 'N/S;',
	`c11` varchar(20) NOT NULL DEFAULT 'N/S;',
	`c12` varchar(20) NOT NULL DEFAULT 'N/S;',
  `A_LAST_COST_COUNT_DATE` varchar(30) NOT NULL DEFAULT 'N/S;',
  `A_LAST_COST_COUNT_ID_GROUP` varchar(11) NOT NULL DEFAULT 'N/S;',
  `A_RECORD_CREATE_DATE` varchar(30) NOT NULL DEFAULT 'N/S;',
  `A_RECORD_CREATE_AUTHOR` varchar(40) NOT NULL DEFAULT 'N/S;',
  `A_RECORD_UPDATE_DATE` varchar(30) NOT NULL DEFAULT 'N/S;',
  `A_RECORD_UPDATE_AUTHOR` varchar(40) NOT NULL DEFAULT 'N/S;',
  `A_STATUS` varchar(100) NOT NULL DEFAULT 'N/S;',
  `A_ADM_COMPANY` varchar(255) DEFAULT 'N/S;',
  `A_CLASSIFIED` varchar(255) DEFAULT 'N/S;',
  PRIMARY KEY (`ID`),
  KEY `ID_USERS2` (`ID_USERS2`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin2;
EOF_SQL_HIST_CREATE;
		$sqlCreate = <<<EOF_SQL_CREATE
CREATE TABLE IF NOT EXISTS `ZALICZKI_NAJEMCOW` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ID_BILLING_USERS` int(11) NOT NULL DEFAULT 0,
  `NAJEMCA` varchar(255) NOT NULL DEFAULT '',
  `T_TELBOX_NEIGHBOUR_IN` varchar(32) NOT NULL DEFAULT '',
  `NR_LOKALU` varchar(32) NOT NULL DEFAULT '',
  `ROK` int(4) NOT NULL,
  `CZY_ROZLICZENIE` enum('TAK','NIE') NOT NULL DEFAULT 'NIE',
  `POW_CALK` double NOT NULL,
  `POW_LOKALU` double NOT NULL,
  `KOSZT_OD` date NOT NULL,
  `KOSZT_DO` date NOT NULL,
	`m01` decimal(16,2) NOT NULL DEFAULT 0,
	`m02` decimal(16,2) NOT NULL DEFAULT 0,
	`m03` decimal(16,2) NOT NULL DEFAULT 0,
	`m04` decimal(16,2) NOT NULL DEFAULT 0,
	`m05` decimal(16,2) NOT NULL DEFAULT 0,
	`m06` decimal(16,2) NOT NULL DEFAULT 0,
	`m07` decimal(16,2) NOT NULL DEFAULT 0,
	`m08` decimal(16,2) NOT NULL DEFAULT 0,
	`m09` decimal(16,2) NOT NULL DEFAULT 0,
	`m10` decimal(16,2) NOT NULL DEFAULT 0,
	`m11` decimal(16,2) NOT NULL DEFAULT 0,
	`m12` decimal(16,2) NOT NULL DEFAULT 0,
	`c01` decimal(16,2) NOT NULL DEFAULT 0,
	`c02` decimal(16,2) NOT NULL DEFAULT 0,
	`c03` decimal(16,2) NOT NULL DEFAULT 0,
	`c04` decimal(16,2) NOT NULL DEFAULT 0,
	`c05` decimal(16,2) NOT NULL DEFAULT 0,
	`c06` decimal(16,2) NOT NULL DEFAULT 0,
	`c07` decimal(16,2) NOT NULL DEFAULT 0,
	`c08` decimal(16,2) NOT NULL DEFAULT 0,
	`c09` decimal(16,2) NOT NULL DEFAULT 0,
	`c10` decimal(16,2) NOT NULL DEFAULT 0,
	`c11` decimal(16,2) NOT NULL DEFAULT 0,
	`c12` decimal(16,2) NOT NULL DEFAULT 0,
  `A_LAST_COST_COUNT_DATE` datetime,
  `A_LAST_COST_COUNT_ID_GROUP` int(11) NOT NULL,
  `A_RECORD_CREATE_DATE` datetime NOT NULL,
  `A_RECORD_CREATE_AUTHOR` varchar(40) NOT NULL,
  `A_RECORD_UPDATE_DATE` datetime,
  `A_RECORD_UPDATE_AUTHOR` varchar(40) NOT NULL,
  `A_STATUS` enum('WAITING','NORMAL','MONITOR','WARNING','OFF_SOFT','OFF_HARD','DELETED') NOT NULL DEFAULT 'WAITING',
  `A_CLASSIFIED` varchar(100) NOT NULL,
  `A_ADM_COMPANY` varchar(100) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin2;
EOF_SQL_CREATE;
		/*
			`ZALICZKI_NAJEMCOW`:
				- who - user login
				- when - shedule (once a day, once on houd)
				- what - action type
				- last_exec_time
		*/
		$pdo->exec($sqlHistCreate);
		$pdo->exec($sqlCreate);
		if($force){
			$pdo->exec("DROP TABLE IF EXISTS ZALICZKI_NAJEMCOW_HIST");
			$pdo->exec("DROP TABLE IF EXISTS ZALICZKI_NAJEMCOW");
			$pdo->exec($sqlHistCreate);
			$pdo->exec($sqlCreate);
		}
		if(1){// TODO: assertTableStruct ZALICZKI_NAJEMCOW
			$expectedStruct = array();
			$expectedStruct['ID'] = array('type'=>'int', 'extra'=>'AUTO_INCREMENT');// `ID` int(11) NOT NULL AUTO_INCREMENT,
			$expectedStruct['ID_BILLING_USERS'] = array('type'=>'int', 'default_value'=>0);// `ID_BILLING_USERS` int(11) NOT NULL DEFAULT 0,
			$expectedStruct['NAJEMCA'] = array('type'=>'varchar', 'max_length'=>'255', 'default_value'=>'');// `NAJEMCA` varchar(255) NOT NULL DEFAULT '',
			$expectedStruct['T_TELBOX_NEIGHBOUR_IN'] = array('type'=>'varchar', 'max_length'=>'32', 'default_value'=>'');// `T_TELBOX_NEIGHBOUR_IN` varchar(32) NOT NULL DEFAULT '',
			$expectedStruct['NR_LOKALU'] = array('type'=>'varchar', 'max_length'=>'32', 'default_value'=>'');// `NR_LOKALU` varchar(255) NOT NULL DEFAULT '',
			$expectedStruct['ROK'] = array('type'=>'int');// `ROK` int(4) NOT NULL,
			$expectedStruct['CZY_ROZLICZENIE'] = array('type'=>'enum', 'values'=>array('TAK','NIE'), 'default_value'=>'NIE');// `CZY_ROZLICZENIE` enum('TAK','NIE') NOT NULL DEFAULT 'NIE',
			$expectedStruct['POW_CALK'] = array('type'=>'double');// `POW_CALK` double NOT NULL,
			$expectedStruct['POW_LOKALU'] = array('type'=>'double');// `POW_LOKALU` double NOT NULL,
			$expectedStruct['KOSZT_OD'] = array('type'=>'date');// `KOSZT_OD` datetime NOT NULL,
			$expectedStruct['KOSZT_DO'] = array('type'=>'date');// `KOSZT_DO` datetime NOT NULL,
			$expectedStruct['m01'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `m01` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['m02'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `m02` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['m03'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `m03` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['m04'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `m04` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['m05'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `m05` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['m06'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `m06` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['m07'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `m07` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['m08'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `m08` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['m09'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `m09` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['m10'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `m10` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['m11'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `m11` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['m12'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `m12` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['c01'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `c01` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['c02'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `c02` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['c03'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `c03` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['c04'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `c04` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['c05'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `c05` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['c06'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `c06` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['c07'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `c07` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['c08'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `c08` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['c09'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `c09` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['c10'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `c10` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['c11'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `c11` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['c12'] = array('type'=>'decimal', 'num_precision'=>16, 'num_scale'=>2);// `c12` decimal(16,2) NOT NULL DEFAULT 0,
			$expectedStruct['A_LAST_COST_COUNT_DATE'] = array('type'=>'datetime', 'is_nullable'=>true);
			$expectedStruct['A_LAST_COST_COUNT_ID_GROUP'] = array('type'=>'int');
			$expectedStruct['A_RECORD_CREATE_DATE'] = array('type'=>'datetime');
			$expectedStruct['A_RECORD_CREATE_AUTHOR'] = array('type'=>'varchar', 'max_length'=>20);
			$expectedStruct['A_RECORD_UPDATE_DATE'] = array('type'=>'datetime', 'is_nullable'=>true);
			$expectedStruct['A_RECORD_UPDATE_AUTHOR'] = array('type'=>'varchar', 'max_length'=>20);
			$expectedStruct['A_STATUS'] = array('type'=>'enum', 'values'=>array('WAITING','NORMAL','MONITOR','WARNING','OFF_SOFT','OFF_HARD','DELETED'), 'default_value'=>'WAITING');
			$expectedStruct['A_CLASSIFIED'] = array('type'=>'varchar');// `A_CLASSIFIED` varchar(100) NOT NULL,
			$expectedStruct['A_ADM_COMPANY'] = array('type'=>'varchar');// `A_ADM_COMPANY` varchar(100) NOT NULL,
			$expectedStruct['key_id'] = array('type'=>'PRIMARY KEY', 'key_fields'=>array('ID'));// PRIMARY KEY (`ID`)

			DBG::_(true, true, "sqlCreate - raw", $sqlCreate, __CLASS__, __FUNCTION__, __LINE__);
			$sqlCreate = $pdo->showCreateStruct('ZALICZKI_NAJEMCOW', $expectedStruct, array('char_encoding'=>'latin2'));
			DBG::_(true, true, "sqlCreate - generated", $sqlCreate, __CLASS__, __FUNCTION__, __LINE__);
			if(0){
				$pdo->assertTableStruct('ZALICZKI_NAJEMCOW', $expectedStruct, array('char_encoding'=>'latin2'));
			}
			if(0){// force - drop/create
				$pdo->exec("DROP TABLE IF EXISTS ZALICZKI_NAJEMCOW");
				$pdo->exec($sqlCreate);
			}
		}
	}

}