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

<?php

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

// Tool for Windykacja status table to store contacts with clients
class Route_UrlAction_UserContact extends RouteBase {

	function defaultAction() { UI::layout([ $this, 'defaultView' ]); }
	function defaultView() {
		$idUser = V::get('id_user', 0, $_GET, 'int');
		if ($idUser <= 0) throw new Exception("Missing id client");

		echo UI::h('h3', [ 'style' => "margin-bottom: 24px; border-bottom: 1px solid #eee" ], "Dodaj notatkę ze spotkania z klientem {$idUser}");
		Windykacja_View::viewUserInfo($idUser);

		$postTask = V::get('_postTask', '', $_POST);
		switch ($postTask) {
			case 'saveConcat': $this->saveContactPostTask($idUser); break;
			case '': break;
			default: throw new Exception("Not implemented postTask '{$postTask}'");
		}
		$this->viewSaveContactForm($idUser);

		echo UI::h('hr');

		$this->viewUserPaymentContactsHistory($idUser);
	}

	function viewSaveContactForm($idUser) {
		$windykInfo = Windykacja_View::getWindykacjaInfo($idUser);
		$windykStatusInfo = ($windykInfo) ? $windykInfo['A_STATUS_INFO'] : '';
		$windykReminder = ($windykInfo && (
			!empty($windykInfo['L_APPOITMENT_USER'])
			|| ( !empty($windykInfo['L_APPOITMENT_DATE']) && '0000-00-00' != $windykInfo['L_APPOITMENT_DATE'] )
			|| !empty($windykInfo['L_APPOITMENT_INFO'])
		)) ? [
			'Pracownik' => $windykInfo['L_APPOITMENT_USER'],
			'Data przypomnienia' => $windykInfo['L_APPOITMENT_DATE'],
			'Adnotacje' => $windykInfo['L_APPOITMENT_INFO'],
		] : null;
		$args = [];
		$args['L_APPOITMENT_USER'] = V::get('L_APPOITMENT_USER', User::getLogin(), $_POST);
		$args['L_APPOITMENT_DATE'] = V::get('L_APPOITMENT_DATE', date("Y-m-d H:i"), $_POST);
		$args['L_APPOITMENT_TYPE'] = V::get('L_APPOITMENT_TYPE', '', $_POST);
		$args['L_APPOITMENT_INFO'] = V::get('L_APPOITMENT_INFO', '', $_POST);
		$args['change_windykacja_status'] = V::get('change_windykacja_status', '', $_POST);
		$args['A_STATUS_INFO'] = V::get('A_STATUS_INFO', $windykStatusInfo, $_POST);
		$args['add_reminder'] = V::get('add_reminder', '', $_POST);
		$args['REMINDER_USER'] = V::get('REMINDER_USER', User::getLogin(), $_POST);
		$args['REMINDER_DATE'] = V::get('REMINDER_DATE', '', $_POST);
		$args['REMINDER_INFO'] = V::get('REMINDER_INFO', '', $_POST);

		echo UI::h('form', [ 'method' => "POST" ], [
			UI::h('input', [ 'type' => "hidden", 'name' => '_postTask', 'value' => "saveConcat" ]),
			UI::h('input', [ 'type' => "hidden", 'name' => 'id_user', 'value' => $idUser ]),
			UI::h('div', [ 'class' => "row", 'style' => "padding-bottom: 12px" ], [
				UI::h('div', [ 'class' => "col-md-6" ], [
					UI::h('label', [], "Pracownik"),
					UI::h('input', [ 'class' => "form-control", 'type' => "text", 'name' => 'L_APPOITMENT_USER', 'value' => $args['L_APPOITMENT_USER'] ]),
				]),
				UI::h('div', [ 'class' => "col-md-3" ], [
					UI::h('label', [], "Data spotkania"),
					UI::h('div', [ 'class' => "input-group" ], [
						UI::h('input', [ 'class' => "form-control se_type-datetime", 'data-format' => "yyyy-MM-dd hh:mm", 'type' => "text", 'name' => 'L_APPOITMENT_DATE', 'value' => $args['L_APPOITMENT_DATE'] ]),
						UI::h('span', [ 'class' => "input-group-addon" ], [
							UI::h('span', [ 'class' => "glyphicon glyphicon-calendar" ]),
						]),
					]),
				]),
				UI::h('div', [ 'class' => "col-md-3" ], [
					UI::h('label', [], "Rodzaj spotkania"),
					UI::h('select', [ 'class' => "form-control", 'name' => 'L_APPOITMENT_TYPE', 'onChange' => "p5_addUserNote_handleChangeType(this)" ], [
						UI::h('option', [ 'value' => "" ]),
						UI::h('option', array_merge([ 'value' => 'LIVE' ], ('LIVE' === $args['L_APPOITMENT_TYPE']) ? [ 'selected' => "selected" ] : []), "LIVE"),
						UI::h('option', array_merge([ 'value' => 'TEL' ], ('TEL' === $args['L_APPOITMENT_TYPE']) ? [ 'selected' => "selected" ] : []), "TEL"),
						UI::h('option', array_merge([ 'value' => 'TEL_NIE_ODBIERA' ], ('TEL_NIE_ODBIERA' === $args['L_APPOITMENT_TYPE']) ? [ 'selected' => "selected" ] : []), "TEL_NIE_ODBIERA"),
						UI::h('option', array_merge([ 'value' => 'MAIL' ], ('MAIL' === $args['L_APPOITMENT_TYPE']) ? [ 'selected' => "selected" ] : []), "MAIL"),
						UI::h('option', array_merge([ 'value' => 'SMS' ], ('SMS' === $args['L_APPOITMENT_TYPE']) ? [ 'selected' => "selected" ] : []), "SMS"),
						UI::h('option', array_merge([ 'value' => 'INNE' ], ('INNE' === $args['L_APPOITMENT_TYPE']) ? [ 'selected' => "selected" ] : []), "INNE"),
					]),
				]),
			]),
			UI::h('div', [ 'class' => "row", 'style' => "padding-bottom: 12px" ], [
				UI::h('div', [ 'class' => "col-md-12" ], [
					UI::h('label', [], "Notatka ze spotkania"),
					UI::h('textarea', array_merge(
						[ 'class' => "form-control", 'type' => "text", 'name' => 'L_APPOITMENT_INFO', 'rows' => 3],
						('TEL_NIE_ODBIERA' === $args['L_APPOITMENT_TYPE']) ? [ 'disabled' => 'disabled' ] : []
					), $args['L_APPOITMENT_INFO']),
				]),
			]),
			UI::h('div', [ 'class' => "row", 'style' => "padding-bottom: 12px" ], [
				UI::h('div', [ 'class' => "col-md-12", 'style' => "border:1px solid #eee" ], [
					UI::h('div', [ 'class' => "checkbox" ], [
						UI::h('label', [ 'style' => "font-size:14px; line-height:22px" ], [
							UI::h('input', array_merge([
								'type' => "checkbox",
								'name' => "change_windykacja_status",
								'onClick' => "document.getElementById('fld__change_windykacja_status').style.display = event.target.checked ? 'block' : 'none'",
							], ($args['change_windykacja_status']) ? [ 'checked' => "checked" ] : [])),
							" Zmień status windykacji (ustalenia z klientem)",
						]),
					]),
					UI::h('div', [
						'id' => "fld__change_windykacja_status",
						'style' => "display:" . ($args['change_windykacja_status'] ? 'block' : 'none') . "; padding-bottom:12px",
					], [
						// UI::h('label', [], "Status windykacji:"),
						UI::h('p', [], [
							"Obecny status: ",
							($windykStatusInfo) ? $windykStatusInfo : "<em>brak</em>",	
						]),
						UI::h('input', [ 'class' => "form-control", 'type' => "text", 'name' => 'A_STATUS_INFO', 'value' => $args['A_STATUS_INFO'] ]),
					]),
				]),
			]),
			UI::h('div', [ 'class' => "row", 'style' => "padding-bottom: 12px" ], [
				UI::h('div', [ 'class' => "col-md-12", 'style' => "border:1px solid #eee" ], [
					UI::h('div', [ 'class' => "checkbox" ], [
						UI::h('label', [ 'style' => "font-size:14px; line-height:22px" ], [
							UI::h('input', array_merge([
								'type' => "checkbox",
								'name' => "add_reminder",
								'onClick' => "document.getElementById('fld__add_reminder').style.display = event.target.checked ? 'block' : 'none'",
							], ($args['add_reminder']) ? [ 'checked' => "checked" ] : [])),
							" Przypomnienie",
						]),
					]),
					UI::h('div', [
						'id' => "fld__add_reminder",
						'style' => "display:" . ($args['add_reminder'] ? 'block' : 'none') . "; padding-bottom:4px",
					], [
						($windykReminder) ? UI::h('div', [ 'style' => "border-left: 5px solid orange; padding:6px 12px; margin-bottom:12px" ], [
							UI::h('p', [], "Obecne przypomnienie (Uwaga: zostanie nadpisane)"),
							UI::hTable([ 'rows' => [ $windykReminder ], 'disable_lp' => true, 'style' => "margin-bottom:0" ]),
						]) : '',
						UI::h('div', [ 'class' => "row", 'style' => "padding-bottom: 12px" ], [
							UI::h('div', [ 'class' => "col-md-6" ], [
								UI::h('label', [], "Pracownik"),
								UI::h('input', [ 'class' => "form-control", 'type' => "text", 'name' => 'REMINDER_USER', 'value' => $args['REMINDER_USER'] ]),
							]),
							UI::h('div', [ 'class' => "col-md-3" ], [
								UI::h('label', [], "Data przypomnienia"),
								UI::h('div', [ 'class' => "input-group" ], [
									UI::h('input', [ 'class' => "form-control se_type-datetime", 'data-format' => "yyyy-MM-dd hh:mm", 'type' => "text", 'name' => 'REMINDER_DATE', 'value' => $args['REMINDER_DATE'] ]),
									UI::h('span', [ 'class' => "input-group-addon" ], [
										UI::h('span', [ 'class' => "glyphicon glyphicon-calendar" ]),
									]),
								]),
							]),
						]),
						UI::h('div', [ 'class' => "row", 'style' => "padding-bottom: 12px" ], [
							UI::h('div', [ 'class' => "col-md-9" ], [
								UI::h('label', [], "Adnotacje"),
								UI::h('textarea', [ 'class' => "form-control", 'type' => "text", 'name' => 'REMINDER_INFO' ], $args['REMINDER_INFO']),
							]),
						]),
					]),
				]),
			]),
			// <div class="form-group">
			// 	<div class="col-sm-offset-2 col-sm-10">
			// 	<div class="checkbox">
			// 		<label>
			// 		<input type="checkbox"> Remember me
			// 		</label>
			// 	</div>
			// 	</div>
			// </div>
			UI::h('div', [ 'class' => "row", 'style' => "padding-bottom: 12px" ], [
				UI::h('div', [ 'class' => "col-md-12" ], [
					UI::h('button', [ 'class' => "btn btn-primary" ], "Zapisz notatkę"),
				]),
			]),
		]);
		echo UI::h('script', [], "
			var DBG1 = true;
			var DBG = false;

			function p5_addUserNote_handleChangeType(n) {
				DBG && console.log('DBG:p5_addUserNote_handleChangeType', { value: n.value, desc: n.form['L_APPOITMENT_INFO'] , n, form: n.form });
				if ('TEL_NIE_ODBIERA' === n.value) {
					n.form['L_APPOITMENT_INFO'].disabled = true
				} else {
					n.form['L_APPOITMENT_INFO'].disabled = false
				}
			}
		");
	}
	function saveContactPostTask($idUser) {
		$args = [];
		$args['L_APPOITMENT_USER'] = V::get('L_APPOITMENT_USER', '', $_POST);
		$args['L_APPOITMENT_DATE'] = V::get('L_APPOITMENT_DATE', '', $_POST);
		$args['L_APPOITMENT_TYPE'] = V::get('L_APPOITMENT_TYPE', '', $_POST);
		$args['L_APPOITMENT_INFO'] = V::get('L_APPOITMENT_INFO', '', $_POST);
		$args['change_windykacja_status'] = V::get('change_windykacja_status', '', $_POST);
		$args['A_STATUS_INFO'] = V::get('A_STATUS_INFO', '', $_POST);
		$args['add_reminder'] = V::get('add_reminder', '', $_POST);
		$args['REMINDER_USER'] = V::get('REMINDER_USER', '', $_POST);
		$args['REMINDER_DATE'] = V::get('REMINDER_DATE', '', $_POST);
		$args['REMINDER_INFO'] = V::get('REMINDER_INFO', '', $_POST);

		try {
			if (empty($args['L_APPOITMENT_USER'])) throw new Exception("Brak pracownika");
			if (empty($args['L_APPOITMENT_DATE'])) throw new Exception("Brak daty spotkania");
			if (empty($args['L_APPOITMENT_TYPE'])) throw new Exception("Brak rodzaju spotkania");
			if (empty($args['L_APPOITMENT_INFO']) && 'TEL_NIE_ODBIERA' !== $args['L_APPOITMENT_TYPE']) throw new Exception("Brak treści notatki");
			if ($args['change_windykacja_status']) {
				if (empty($args['A_STATUS_INFO'])) throw new Exception("Brak statusu windykacji");
			}
			if ($args['add_reminder']) {
				if (empty($args['REMINDER_USER'])) throw new Exception("Brak pracownika w przypomnieniu");
				if (empty($args['REMINDER_DATE'])) throw new Exception("Brak daty przypomnienia");
				if (empty($args['REMINDER_INFO'])) throw new Exception("Brak adnotacji przypomnienia");
			}

			$windykInfo = Windykacja_View::getWindykacjaInfo($idUser);

			// create new record with L_APPOITMENT_* in table `USERS2_CONTACT`
			$newContactItem = [
				'ID_BILLING_USERS' => $idUser,
				'L_APPOITMENT_USER' => $args['L_APPOITMENT_USER'],
				'L_APPOITMENT_DATE' => $args['L_APPOITMENT_DATE'],
				'L_APPOITMENT_TYPE' => $args['L_APPOITMENT_TYPE'],
				'L_APPOITMENT_INFO' => $args['L_APPOITMENT_INFO'],
				'SALDO' => $windykInfo['PAY_SALDO'],
				'A_RECORD_CREATE_DATE' => "NOW()",
				'A_RECORD_CREATE_AUTHOR' => User::getLogin(),
			];
			$idCreated = DB::getPDO()->insert('USERS2_CONTACT', $newContactItem);
			DB::getPDO()->insert('USERS2_CONTACT_HIST', array_merge($newContactItem, [ 'ID_USERS2' => $idCreated ]));
			// update A_STATUS_INFO in `USERS2_WINDYKACJA_STATUS` if $args['change_windykacja_status']
			if ($args['change_windykacja_status']) {
				if ($windykInfo['A_STATUS_INFO'] != $args['A_STATUS_INFO']) {
					$toUpdateWindyk = [
						'A_STATUS_INFO' => $args['A_STATUS_INFO'],
						'A_RECORD_UPDATE_DATE' => "NOW()",
						'A_RECORD_UPDATE_AUTHOR' => User::getLogin(),
					];
					DB::getPDO()->update('USERS2_WINDYKACJA_STATUS', 'ID_BILLING_USERS', $idUser, $toUpdateWindyk);
					DB::getPDO()->insert('USERS2_WINDYKACJA_STATUS_HIST', array_merge($toUpdateWindyk, [
						'ID_USERS2' => $windykInfo['ID'],
					]));
				}
			}
			// update L_APPOITMENT_* fields using REMINDER_* in `USERS2_WINDYKACJA_STATUS` if $args['add_reminder']
			if ($args['add_reminder']) {
				DB::getPDO()->update('USERS2_WINDYKACJA_STATUS', 'ID_BILLING_USERS', $idUser, [
					'L_APPOITMENT_USER' => $args['REMINDER_USER'],
					'L_APPOITMENT_DATE' => $args['REMINDER_DATE'],
					'L_APPOITMENT_INFO' => $args['REMINDER_INFO'],
					'A_RECORD_UPDATE_DATE' => "NOW()",
					'A_RECORD_UPDATE_AUTHOR' => User::getLogin(),
				]);
			}
		} catch (Exception $e) {
			DBG::log($e);
			UI::alert('danger', $e->getMessage());
			return;
		}

		throw new AlertSuccessException("Wprowadzono notatkę ze spotkania z klientem " . UI::h('a', [ 'href' => $this->getLink('', [ 'id_user' => $idUser ]) ], "wróć"));
	}

	function viewUserPaymentContactsHistory($idUser, $params = []) {
		$markEventsFromSpotkanieNr = V::get('markEventsFromSpotkanieNr', 0, $params, 'int');

		Lib::loadClass('Windykacja_StatsModel');
		$billDocs = Windykacja_StatsModel::getBillDocsByDate($idUser);

		$lastHist = DB::getPDO()->fetchAll("
			select t.ID
				, t.ID_BILLING_USERS
			--	, t.CLIENT_INFO
				, t.A_STATUS
			--	, t.A_STATUS_INFO
				, t.L_APPOITMENT_USER
				, t.L_APPOITMENT_DATE
				, t.L_APPOITMENT_TYPE
				, t.L_APPOITMENT_INFO
				, t.A_RECORD_CREATE_DATE
			--	, IF('0000-00-00 00:00:00' = t.A_RECORD_UPDATE_DATE, t.A_RECORD_CREATE_DATE, t.A_RECORD_UPDATE_DATE) as A_RECORD_UPDATE_DATE
			from USERS2_CONTACT t
			where t.ID_BILLING_USERS = :id
				and t.A_STATUS != 'DELETED'
			limit 11
		", [ ':id' => $idUser ]);
		// DBG::nicePrint($lastHist, '$lastHist');
		if (!empty($lastHist)) {
			foreach ($lastHist as $bill_doc) {
				$billDocs->add_event(substr($bill_doc['A_RECORD_CREATE_DATE'], 0, 10), 'HIST_CONTACT', $bill_doc);
			}
		}

		// TODO: add zmiania statusu: select h.* from Windyk_HIST where A_STATUS_INFO != 'N/S;'

		$umowy_l2 = Windykacja_StatsModel::getClientUmowy($idUser);
		//echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">billDocs ';print_r($billDocs);echo'</pre>';
		//echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">billDocs ';print_r(reset($billDocs));echo'</pre>';
		//echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">umowy_l2 ';print_r($umowy_l2);echo'</pre>';
		if (!empty($umowy_l2)) {
			foreach ($umowy_l2 as $h) {
				$billDocs->add_event($h['P_DEALDATE'], 'UMOWA', $h);

				if ($h['P_DEALDATE_TERM'] != '0000-00-00') {// < date('Y-m-d')) {
					$billDocs->add_event($h['P_DEALDATE_TERM'], 'KONIEC UMOWY', $h);
				}
			}
		}

		$user_hist_events = Windykacja_StatsHelper::getUserHistStatusEvents($idUser);
		if (!empty($user_hist_events)) {
			foreach ($user_hist_events as $bill_doc) {
				$billDocs->add_event($bill_doc['A_STATUS_UPDATE_DATE'], 'HIST_STATUS', $bill_doc);
			}
		}

		$user_hist_events = Windykacja_StatsHelper::getUserHistPhoneEvents($idUser);
		if (!empty($user_hist_events)) {
			foreach ($user_hist_events as $bill_doc) {
				$billDocs->add_event(substr($bill_doc['A_RECORD_UPDATE_DATE'], 0, 10), 'HIST_PHONE', $bill_doc);
			}
		}

		$user_hist_events = Windykacja_StatsHelper::getUserBadAddressEvents($user->ID);
		if (!empty($user_hist_events)) {
			foreach ($user_hist_events as $bill_doc) {
				$billDocs->add_event(substr($bill_doc['A_RECORD_UPDATE_DATE'], 0, 10), 'HIST_BAD_ADDRESS', $bill_doc);
			}
		}

		// add today
		$bill_doc = array();
		$bill_doc['saldo'] = $billDocs->get_saldo();
		$billDocs->add_event(date('Y-m-d'), 'TODAY', $bill_doc);

		$billDocs->sort_docs();
		$billDocs->set_saldo_for_all_docs();

		$listBillDocs = $billDocs->get_docs(); // [ day => [ Windykacja_EventDoc, ... ], ... ]
		// convert to: [ year => [ Windykacja_EventDoc, ... ] ]
		$groupedByYearBillDocs = array_reduce($listBillDocs, function ($ret, $listDayEvents) {
			foreach ($listDayEvents as $event) {
				$year = substr($event->get_date(), 0, 4);
				if (empty($ret[$year])) $ret[$year] = [];
				array_unshift($ret[$year], $event);
			}
			return $ret;
		}, []);
		$groupedByYearBillDocs = array_reverse($groupedByYearBillDocs, true);

		// DBG::nicePrint($listBillDocs, '$listBillDocs');
		// DBG::nicePrint($groupedByYearBillDocs, '$groupedByYearBillDocs');

		// UI::table([ 'caption' => "Historia kontaktów z klientem (TODO: w trakcie prac. Zobacz " .
		// 	UI::h('a', [ 'href' => "index.php?MENU_INIT=USERS2_WINDYKACJA_STATUS&q=&_f=&_user_id={$idUser}" ], "Panel windykacji") .
		// ")", 'rows' => $lastHist ]);
		// if (count($lastHist) > 10) UI::alert('info', "Przeglądaj wszystkie wpisy TODO: link to view table with ff_ID_BILLING_USERS");
		echo UI::h('div', [], array_merge(
			[
				UI::h('h3', [], "Historia klienta"),
			],
			array_map(function ($listEventDocs, $year) use ($markEventsFromSpotkanieNr) {
				return UI::h('details', array_merge([ 'style' => "margin:8px 0" ],
						(date("Y") == $year) ? [ 'open' => "" ] : []
					), [
					UI::h('summary', [ 'style' => "height:20px; position:relative; cursor:pointer; outline:none" ], [
						UI::h('div', [ 'style' => "position:absolute; top:9px; left:20px; width: 100%; border-bottom:2px solid #000" ]),
						UI::h('span', [ 'style' => "position:absolute; top:0; left:12px; padding:0 12px 0 0; line-height:20px; background-color:#fff; font-weight:bold" ], [
							"{$year}:",
						]),
					]),
					UI::h('div', [ 'style' => "padding: 8px 0px 8px 54px" ],
						array_merge(
							[
								UI::hTable([
									'disable_lp' => true,
									'@class' => 'table table-hover', // table-bordered
									'cols' => [
										'data',
										'winien',
										'ma',
										'saldo',
										'#',
										'uwagi',
									],
									'@style[data]' => "width:80px; text-align:right",
									'@style[winien]' => "width:90px; text-align:right",
									'@style[ma]' => "width:90px; text-align:right",
									'@style[saldo]' => "width:90px; text-align:right; background-color:#f5f5f5",
									'@style[#]' => "width:140px; padding-left:12px; text-align:left",
									'rows' => array_map(function ($eventDoc) use ($markEventsFromSpotkanieNr) {
										$saldo = $eventDoc->get_saldo();
										$overallSaldo = $eventDoc->get_saldo_overall();
										$rowClass = "";
										// $markEventsFromSpotkanieNr
										if ($markEventsFromSpotkanieNr
											&& $eventDoc->get_class() == 'EVENT' && $eventDoc->get_type() == 'HIST_CONTACT'
											&& $eventDoc->get('ID') == $markEventsFromSpotkanieNr
										) {
											$rowClass = "info";
										} else {
											$rowClass = ('EVENT' === $eventDoc->get_class() && 'TODAY' === $eventDoc->get_type()) ? "warning" : "";
										}

										return [
											'data' => $eventDoc->get_date(),
											'winien' => UI::h('span', [
													'style' => "color:" . ( (-1 * $saldo > 0) ? "#000" : "#ddd" ),
												], ($saldo < 0) ? UI::price(-1 * $saldo) : 0
											),
											'ma' => UI::h('span', [
													'style' => "color:" . ( ($saldo > 0) ? "#000" : "#ddd" ),
												], ($saldo > 0) ? UI::price($saldo) : 0
											),
											'saldo' => UI::h('span', [
													'style' => "color:" . ( ($overallSaldo >= 0) ? "green" : "red" ),
												], UI::price($overallSaldo)
											),
											'#' => $this->viewUserPaymentContactsHistory_eventNr($eventDoc),
											'uwagi' => $this->viewUserPaymentContactsHistory_eventLabel($eventDoc),
											'@class' => $rowClass,
											'@style[data]' => "text-align:right",
											'@style[winien]' => "text-align:right",
											'@style[ma]' => "text-align:right",
											'@style[saldo]' => "text-align:right; background-color:#f5f5f5",
											'@style[#]' => "padding-left:12px",
										];
									}, $listEventDocs),
								]),
							],
							[]
						)
					),
				]);
			}, $groupedByYearBillDocs, array_keys($groupedByYearBillDocs))
		));
	}
	function viewUserPaymentContactsHistory_eventNr($v_doc) { // return html (string)
		/* links fomr l1:
		 *   FV: https://l1.webone.pl/modules/make_billing/edit.php?mod=make_billing&form=html_faktura&adm=edit&uid=4014&fpos=&dz=&close=0&doc=799413&doctype=1
		 *   KOR: https://l1.webone.pl/modules/make_billing/edit.php?mod=make_billing&form=html_faktura_korekta&adm=edit&uid=4014&fpos=&dz=&close=0&doc=809557&doctype=3
		 *        https://biuro.biall-net.pl/dev-pl/se-master/index.php?FUNCTION_INIT=bm_show_document&ARG1_VAL=809557
		 */
		if ($v_doc->get_class() == 'BILLING' && 'FVAT' == $v_doc->get_type()) {
			$nr = $v_doc->get_type();
			Lib::loadClass('Windykacja_StatsModel');
			$type_desc = Windykacja_StatsModel::get_billing_type_desc($v_doc->get_type());
			if ($type_desc) {
				$type_desc .= "\n Wystawiona: ".$v_doc->get('BILL_DATE')."\n Termin płatności: ".$v_doc->get('PAYMENT_TERM');
				$nr = '<span title="'.$type_desc.'">'.$nr.'</span>';
				// TODO: podglad faktury
				//if ($h['type'] == 'FVAT') $nr .= ' <a href="'."?MENU_INIT=USERS2_WINDYKACJA_STATUS&_user_id=".$user->ID."&task=view_faktura&id=".$h['ID'].'" target="_blank">'.'<img src="'."icon/search.png".'" alt="'."U".'" title="'."Podgląd faktury".'" />'.'</a>';
			}
			$docNr = $v_doc->get('nr');
			$docNrLabel = $v_doc->get('NUMBER') . '/' . $v_doc->get('ID_BILLING_PREFIXES');
			$nr .= ' <a href="index.php?FUNCTION_INIT=bm_show_document&ARG1_VAL=' . $docNr . '" target="_blank">'."({$docNrLabel})".'</a>';

			return $nr;
		}
		if ($v_doc->get_class() == 'BILLING' && 'KORV' == $v_doc->get_type()) {
			$nr = $v_doc->get_type();
			Lib::loadClass('Windykacja_StatsModel');
			$type_desc = Windykacja_StatsModel::get_billing_type_desc($v_doc->get_type());
			if ($type_desc) {
				$nr = '<span title="'.$type_desc.'">'.$nr.'</span>';
			}
			$docNr = $v_doc->get('nr');
			$docNrLabel = $v_doc->get('NUMBER') . '/' . $v_doc->get('ID_BILLING_PREFIXES');
			$remoteFvNum = $v_doc->get('FV_NUMBER');
			$korNr = $v_doc->get('ID_BILLING_NUMBERS');
			$korTitle = "Korekta dotycząca faktury nr: {$remoteFvNum}";
			$nr .= ' <a href="index.php?FUNCTION_INIT=bm_show_document&ARG1_VAL=' . $korNr . '" target="_blank" title="'.$korTitle.'">'."({$docNrLabel})".'</a>';

			return $nr;
		}
		if ($v_doc->get_class() == 'BILLING') {
			$nr = $v_doc->get_type();
			Lib::loadClass('Windykacja_StatsModel');
			$type_desc = Windykacja_StatsModel::get_billing_type_desc($v_doc->get_type());
			if ($type_desc) {
				$nr = '<span title="'.$type_desc.'">'.$nr.'</span>';
			}
			$docNr = $v_doc->get('nr');
			$docNrLabel = $v_doc->get('NUMBER') . '/' . $v_doc->get('ID_BILLING_PREFIXES');

			return $nr;
		}
		if ($v_doc->get_class() == 'EVENT' && $v_doc->get_type() == 'TODAY') return "";
		if ($v_doc->get_class() == 'EVENT' && $v_doc->get_type() == 'HIST_STATUS') return "";
		if ($v_doc->get_class() == 'EVENT' && $v_doc->get_type() == 'HIST_PHONE') return "";
		if ($v_doc->get_class() == 'EVENT' && $v_doc->get_type() == 'HIST_BAD_ADDRESS') return "";
		if ($v_doc->get_class() == 'EVENT' && $v_doc->get_type() == 'HIST_CONTACT') return "Spotkanie " . UI::h('a', [
			'href' => "index.php?_route=ViewTableAjax&namespace=default_db/USERS2_CONTACT#EDIT/" . $v_doc->get('ID'),
			'target' => "_blank",
			'title' => "Notatka ze spotkania",
		], "(" . $v_doc->get('ID') . ")");
		if ($v_doc->get_class() == 'EVENT') return UI::h('span', [ 'title' => $v_doc->get('ID') ], $v_doc->get_type()); // TODO: dla KP,KW: ID_BILLING_NUMBERS zamiast ID

		return "???";
	}
	function viewUserPaymentContactsHistory_eventLabel($v_doc) { // return html (string)
		if ($v_doc->get_class() == 'BILLING') return '';
		if ($v_doc->get_class() == 'EVENT' && $v_doc->get_type() == 'TODAY') return '';
		if ($v_doc->get_class() == 'EVENT' && $v_doc->get_type() == 'HIST_STATUS') {
			$id_koresp = intval($v_doc->get('ID_KORESP'));
			$add = '';
			if ($id_koresp > 0) {
				$koresp_edit_link = "index.php?_route=ViewTableAjax&namespace=default_db/IN7_DZIENNIK_KORESP#EDIT/{$id_koresp}";
				$add = " " . '<a href="' . $koresp_edit_link . '" title="' . "Edycja KORESP. ({$id_koresp})" . '" target="_blank">' . "KOR" . '</a>';
			}
			switch ($v_doc->get('A_STATUS')) {
				case 'WAITING': return "powrót do oczekujących {$add}";
				case 'waiting-wezwanie2':return "wezwanie do zapłaty (Nr koresp.: ".$v_doc->get('ID_KORESP').") <em>(termin: ".$v_doc->get('PAY_TERM').")</em> {$add}";
				case 'waiting-krd': return "wezwanie do zapłaty ost. (Nr koresp.: ".$v_doc->get('ID_KORESP').") <em>(termin: ".$v_doc->get('PAY_TERM').")</em> {$add}";
				case 'N/S;': return "Nr koresp.: ".$v_doc->get('ID_KORESP')." <em>(" . $v_doc->get('K_ZAWARTOS') . ")</em>" .
					(
						($v_doc->get('ID_KORESP') > 0 && substr($v_doc->get('params'), 0, strlen('rozwiazanie umowy')) == 'rozwiazanie umowy')
						?	" " . '<a href="'."?MENU_INIT=USERS2_WINDYKACJA_STATUS&q=".V::get('q','', $_REQUEST)."&task="."bok_rozwiazanie_umowy_print"."&user_id={$user->ID}&id_koresp=".$v_doc->get('ID_KORESP').'" target="_blank" title="' . "Generuj druk rozwiązania umowy" . '">' . "druk" . '</a>'
						:	""
					) . " {$add}"
				;
				// TODO: get opis from koresp
				//echo '('.$v_doc->get('ID_KORESP').', '.$v_doc->get('K_ZAWARTOS').'='.substr($v_doc->get('K_ZAWARTOS'), 0, strlen('rozwiazanie umowy')).')';
				default: return "zmiana statusu na: " . $v_doc->get('A_STATUS') . " ";
			}
		}
		if ($v_doc->get_class() == 'EVENT' && $v_doc->get_type() == 'HIST_PHONE') {
			$lastUpdateDate = $v_doc->get('A_RECORD_UPDATE_DATE');

			$lastPhoneStatus = ('N/S;' === $v_doc->get('LAST_PHONE_STATUS')) ? '' : $v_doc->get('LAST_PHONE_STATUS');
			if (!empty($lastPhoneStatus)) {
				$date = $v_doc->get('PAY_TERM');
				if ($date == 'N/S;') $date = '';
				switch ($lastPhoneStatus) {
					case 'nie_zaplaci': return "nie zapłaci";
					case 'zaplaci_w_terminie': return "zapłaci w terminie {$date} " . '<em style="font-size:small;">' . "(ustalono {$lastUpdateDate})" . '</em>';
					case 'zaplaci_za_1mc': return "zapłaci miesiąc później {$date} " . '<em style="font-size:small;">' . "(ustalono {$lastUpdateDate})" . '</em>';
					case 'zaplaci_za_2mc': return "zapłaci 2 miesiące później {$date} " . '<em style="font-size:small;">' . "(ustalono {$lastUpdateDate})" . '</em>';
					case 'zaplaci_za_3mc': return "zapłaci 3 miesiące później {$date} " . '<em style="font-size:small;">' . "(ustalono {$lastUpdateDate})" . '</em>';
					default: return "kontakt z klientem: {$lastPhoneStatus} " . '<em style="font-size:small;">' . "(ustalono {$lastUpdateDate})" . '</em>';
				}
			}

			$lastSmsStatus = $v_doc->get('LAST_SMS_STATUS');
			if ($lastSmsStatus == 'N/S;') $lastSmsStatus = '';
			if (!empty($lastSmsStatus)) {
				$lastSmsId = $v_doc->get('LAST_SMS_MSG_ID');
				return "SMS: {$lastSmsStatus} " . '<em style="font-size:small;" title="' . "(wysłano {$lastUpdateDate}, {$lastSmsId})" . '">' . "(wysłano {$lastUpdateDate})" . '</em>';
			}

			$lastMailStatus = $v_doc->get('LAST_MAIL_STATUS');
			if ($lastMailStatus == 'N/S;') $lastMailStatus = '';
			if (!empty($lastMailStatus)) {
				$lastMailId = $v_doc->get('LAST_MAIL_MSG_ID');
				return "MAIL: {$lastMailStatus} " . '<em style="font-size:small;" title="' . "(wysłano {$lastUpdateDate}, {$lastMailId})" . '">' . "(wysłano {$lastUpdateDate})" . '</em>';
			}
		}

		if ($v_doc->get_class() == 'EVENT' && $v_doc->get_type() == 'HIST_BAD_ADDRESS') {
			$koresp_edit_link = "index.php?_route=ViewTableAjax&namespace=default_db/IN7_DZIENNIK_KORESP#EDIT/";
			$add = ($v_doc->get('ID_KORESP') > 0)
				?	" - ID Koresp. (" . '<a href="' . $koresp_edit_link . $v_doc->get('ID_KORESP') . '" target="_blank">' . $v_doc->get('ID_KORESP') . '</a>' . ")"
				:	""
			;

			return ($v_doc->get('BAD_ADDRESS'))
				?	"błędny adres zameldowania {$add}"
				:	"poprawa adresu zameldowania {$add}"
			;
		}

		if ($v_doc->get_class() == 'EVENT' && $v_doc->get_type() == 'UMOWA') {
			return "umowa nr <b>".$v_doc->get('P_DEALNUMBER')."</b> <em>(termin ".$v_doc->get('P_DEALDATE_TERM').")</em>";
		}

		if ($v_doc->get_class() == 'EVENT' && $v_doc->get_type() == 'KONIEC UMOWY') {
			return "zakończenie umowy nr <b>".$v_doc->get('P_DEALNUMBER')."</b> <em>(z dnia ".$v_doc->get('P_DEALDATE').")</em>";
		}

		if ($v_doc->get_class() == 'EVENT' && $v_doc->get_type() == 'HIST_CONTACT') {
			return implode(" ", [
				$v_doc->get('L_APPOITMENT_TYPE'),
				$v_doc->get('L_APPOITMENT_INFO'),
				UI::h('em', [], "(" . $v_doc->get('L_APPOITMENT_USER') . ")"),
			]);
		}

		return "...";

	}

	function prepareTable() {
		DB::getPDO()->execSql("
			CREATE TABLE IF NOT EXISTS `USERS2_CONTACT` (
				`ID` int(11) NOT NULL AUTO_INCREMENT,
				`ID_BILLING_USERS` int(11) NOT NULL DEFAULT '0',
				`CLIENT_INFO` varchar(255) NOT NULL DEFAULT '',
				`CLIENT_TEL` varchar(255) NOT NULL DEFAULT '',
				`A_STATUS` enum('WAITING','NORMAL','DELETED') NOT NULL DEFAULT 'WAITING',
				`A_STATUS_INFO` varchar(255) NOT NULL,
				`L_APPOITMENT_DATE` date NOT NULL DEFAULT '0000-00-00',
				`L_APPOITMENT_USER` varchar(20) NOT NULL DEFAULT '',
				`L_APPOITMENT_PERIOD` varchar(4) NOT NULL,
				`L_APPOITMENT_INFO` varchar(255) NOT NULL,
				`L_APPOITMENT_TYPE` enum('','LIVE','TEL','TEL_NIE_ODBIERA','MAIL','SMS','INNE') DEFAULT NULL,
				`A_RECORD_CREATE_DATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
				`A_RECORD_CREATE_AUTHOR` varchar(20) NOT NULL DEFAULT '',
				`A_RECORD_UPDATE_DATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
				`A_RECORD_UPDATE_AUTHOR` varchar(20) NOT NULL DEFAULT '',
				`A_PROBLEM` enum('','WARNING','PROBLEM','SERIOUS','UNVERIFIED') DEFAULT NULL,
				`A_PROBLEM_DESC` varchar(255) NOT NULL DEFAULT '',
				`A_PROBLEM_DATE` varchar(30) NOT NULL DEFAULT '',
				`T_WORKPOINTS` varchar(100) NOT NULL DEFAULT '',
				`T_WORKPOINTS_VALUE` varchar(100) NOT NULL DEFAULT '',
				`T_WORKPOINTS_TYPE` varchar(100) NOT NULL DEFAULT '',
				`T_WORKPOINTS_USER` varchar(100) NOT NULL DEFAULT '',
				`T_WORKPOINTS_DATE` varchar(100) NOT NULL DEFAULT '',
				`A_CLASSIFIED` varchar(100) NOT NULL DEFAULT '',
				`A_ADM_COMPANY` varchar(100) NOT NULL DEFAULT '',
				`ROZLICZ_MONTH` date NOT NULL DEFAULT '0000-00-00',
				`ROZLICZ_CONFIRM` int(11) NOT NULL DEFAULT '0',
				`SALDO` decimal(10,2) NOT NULL DEFAULT '0.00',
				`ILE_ODZYSKANO` decimal(10,2) NOT NULL DEFAULT '0.00',
				PRIMARY KEY (`ID`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
		");
		DB::getPDO()->execSql("
			CREATE TABLE IF NOT EXISTS `USERS2_CONTACT_HIST` (
				`ID` int(11) NOT NULL AUTO_INCREMENT,
				`ID_USERS2` int(11) NOT NULL,
				`ID_BILLING_USERS` varchar(11) NOT NULL DEFAULT 'N/S;',
				`CLIENT_INFO` varchar(255) NOT NULL DEFAULT 'N/S;',
				`CLIENT_TEL` varchar(255) NOT NULL DEFAULT 'N/S;',
				`A_STATUS` varchar(32) NOT NULL DEFAULT 'N/S;',
				`A_STATUS_INFO` varchar(255) NOT NULL DEFAULT 'N/S;',
				`L_APPOITMENT_DATE` varchar(20) NOT NULL DEFAULT 'N/S;',
				`L_APPOITMENT_USER` varchar(20) NOT NULL DEFAULT 'N/S;',
				`L_APPOITMENT_PERIOD` varchar(4) NOT NULL DEFAULT 'N/S;',
				`L_APPOITMENT_INFO` varchar(255) NOT NULL DEFAULT 'N/S;',
				`L_APPOITMENT_TYPE` varchar(16) NOT NULL DEFAULT 'N/S;',
				`A_RECORD_CREATE_DATE` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_RECORD_CREATE_AUTHOR` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_RECORD_UPDATE_DATE` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_RECORD_UPDATE_AUTHOR` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_PROBLEM` varchar(16) NOT NULL DEFAULT 'N/S;',
				`A_PROBLEM_DESC` varchar(255) NOT NULL DEFAULT 'N/S;',
				`A_PROBLEM_DATE` varchar(30) NOT NULL DEFAULT 'N/S;',
				`T_WORKPOINTS` varchar(100) NOT NULL DEFAULT 'N/S;',
				`T_WORKPOINTS_VALUE` varchar(100) NOT NULL DEFAULT 'N/S;',
				`T_WORKPOINTS_TYPE` varchar(100) NOT NULL DEFAULT 'N/S;',
				`T_WORKPOINTS_USER` varchar(100) NOT NULL DEFAULT 'N/S;',
				`T_WORKPOINTS_DATE` varchar(100) NOT NULL DEFAULT 'N/S;',
				`A_CLASSIFIED` varchar(100) NOT NULL DEFAULT 'N/S;',
				`A_ADM_COMPANY` varchar(100) NOT NULL DEFAULT 'N/S;',
				`ROZLICZ_MONTH` varchar(20) NOT NULL DEFAULT 'N/S;',
				`ROZLICZ_CONFIRM` varchar(11) NOT NULL DEFAULT 'N/S;',
				`SALDO` varchar(11) NOT NULL DEFAULT 'N/S;',
				`ILE_ODZYSKANO` varchar(11) NOT NULL DEFAULT 'N/S;',
				PRIMARY KEY (`ID`),
				KEY `ID_USERS2` (`ID_USERS2`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
		");
	}
}

/*
insert into USERS2_CONTACT (
    A_RECORD_CREATE_DATE,
    A_RECORD_CREATE_AUTHOR,
    ID_BILLING_USERS,
    CLIENT_INFO,
    L_APPOITMENT_TYPE,
    L_APPOITMENT_DATE,
    L_APPOITMENT_USER,
    L_APPOITMENT_INFO
)
select
    h.A_RECORD_UPDATE_DATE as A_RECORD_CREATE_DATE,
    h.A_RECORD_UPDATE_AUTHOR as A_RECORD_CREATE_AUTHOR,
    ( select w.ID_BILLING_USERS from USERS2_WINDYKACJA_STATUS w where w.ID = h.ID_USERS2 ) as ID_BILLING_USERS,
    '' as CLIENT_INFO,
	'TEL' as L_APPOITMENT_TYPE,
    IF('N/S;' = h.L_APPOITMENT_DATE and h.LAST_PHONE_STATUS_DATE != 'N/S;', h.LAST_PHONE_STATUS_DATE, h.L_APPOITMENT_DATE) as L_APPOITMENT_DATE,
    IF('N/S;' = h.L_APPOITMENT_DATE and h.LAST_PHONE_STATUS_DATE != 'N/S;', h.A_RECORD_UPDATE_AUTHOR, h.L_APPOITMENT_USER) as L_APPOITMENT_USER,
    IF('N/S;' = h.L_APPOITMENT_DATE and h.LAST_PHONE_STATUS_DATE != 'N/S;', h.LAST_PHONE_STATUS, h.L_APPOITMENT_INFO) as L_APPOITMENT_INFO

--    , '===' as X, h.*

from USERS2_WINDYKACJA_STATUS_HIST h
where
    h.A_RECORD_UPDATE_DATE like '2019-08-%'
    and h.A_RECORD_UPDATE_AUTHOR not like '%update%'
    and h.ID_KORESP = 'N/S;' -- params like 'rowzwiaanie umowy%'

select sum(ILE_ODZYSKANO) from USERS2_CONTACT;


select count(DISTINCT ID_BILLING_USERS) from USERS2_CONTACT;


select ID_BILLING_USERS, count(*) as cnt from USERS2_CONTACT group by ID_BILLING_USERS;


 * fix rows order and HIST

insert into USERS2_CONTACT_temp201909 (
	ID_OLD,
    A_RECORD_CREATE_DATE,
    A_RECORD_CREATE_AUTHOR,
    ID_BILLING_USERS,
    CLIENT_INFO,
    L_APPOITMENT_TYPE,
    L_APPOITMENT_DATE,
    L_APPOITMENT_USER,
    L_APPOITMENT_INFO
)
select t.*
from
(
select
	0 as ID_OLD,
    h.A_RECORD_UPDATE_DATE as A_RECORD_CREATE_DATE,
    h.A_RECORD_UPDATE_AUTHOR as A_RECORD_CREATE_AUTHOR,
    ( select w.ID_BILLING_USERS from USERS2_WINDYKACJA_STATUS w where w.ID = h.ID_USERS2 ) as ID_BILLING_USERS,
    '' as CLIENT_INFO,
	'TEL' as L_APPOITMENT_TYPE,
    IF('N/S;' = h.L_APPOITMENT_DATE and h.LAST_PHONE_STATUS_DATE != 'N/S;', h.LAST_PHONE_STATUS_DATE, h.L_APPOITMENT_DATE) as L_APPOITMENT_DATE,
    IF('N/S;' = h.L_APPOITMENT_DATE and h.LAST_PHONE_STATUS_DATE != 'N/S;', h.A_RECORD_UPDATE_AUTHOR, h.L_APPOITMENT_USER) as L_APPOITMENT_USER,
    IF('N/S;' = h.L_APPOITMENT_DATE and h.LAST_PHONE_STATUS_DATE != 'N/S;', h.LAST_PHONE_STATUS, h.L_APPOITMENT_INFO) as L_APPOITMENT_INFO
from USERS2_WINDYKACJA_STATUS_HIST h
where
    h.A_RECORD_UPDATE_DATE like '2019-09-%'
    and h.A_RECORD_UPDATE_AUTHOR not like '%update%'
    and h.ID_KORESP = 'N/S;' -- params like 'rowzwiaanie umowy%'
union

select
	c.ID as ID_OLD,
    c.A_RECORD_CREATE_DATE,
    c.A_RECORD_CREATE_AUTHOR,
    c.ID_BILLING_USERS,
    c.CLIENT_INFO,
    c.L_APPOITMENT_TYPE,
    c.L_APPOITMENT_DATE,
    c.L_APPOITMENT_USER,
    c.L_APPOITMENT_INFO
from USERS2_CONTACT c
where c.ID >= 28
) as t
order by t.A_RECORD_CREATE_DATE



*/