se/SE/se-lib/Windykacja/StatsHelper.php

<?php

Lib::loadClass('Windykacja_StatsModel');
Lib::loadClass('Windykacja_FunkcjeL1');

class Windykacja_StatsHelper {

	// TODO: `A_RECORD_UPDATE_DATE` aktualizowane tylko wraz ze statusem
	// TODO: statusy:
		// 1. Automatyczne wysłanie powiadomień do Abonentów o niezapłaconych fakturach drogą e-mailową
		//    5 dni roboczych po 10-go na okolo 5 dni przed blokadami
		// - blokada: ??? dni po ostatniej dacie platnosci - odczytac czy zablokowany z l2?
		// - 1 list - wezwanie - jesli min. 2 faktury - czyli 2 m-ce od 1 daty platnosci
		// - 2 list - wezwanie ostateczne - min. 300 zł

	public static function get_filter_selected() {
		$filter_selected = V::get('_f', '', $_GET);
		$filter_arr = self::get_filters();
		$filter_selected = (array_key_exists($filter_selected, $filter_arr))? $filter_selected : '';
		return $filter_selected;
	}

	public static function get_filter_selected_label() {
		$fltrLabel = '';
		$fltrSelected = self::get_filter_selected();
		if (!empty($fltrSelected)) {
			$fltrs = self::get_filters();
			$fltrLabel = V::get($fltrSelected, '', $fltrs);
			$fltrLabel = V::get(0, '', $fltrLabel);
		}
		return $fltrLabel;
	}

	public static function get_filters() {
		$filter_arr['stan_zero'] = array("stan zerowy");
		$filter_arr['10-ego'] = array("termin płatności faktur, saldo na -", 'desc'=>array("10-ego", "10-ego każdego miesiąca - termin płatnosci faktur oraz saldo na minusie"));
		$filter_arr['WAITING'] = array("termin płatności faktur", 'desc'=>array("10-ego", "10-ego każdego miesiąca - termin płatnosci faktur"));// z regulaminu
		$filter_arr['15'] = array("mail ponaglenie", 'desc'=>array("10-ego + 5", "10-ego + 5 dni roboczych"));
		$filter_arr['blokada'] = array("blokada", 'desc'=>array("pn, wt, śr około 25", "najbliższy pn, wt, śr do 25"));// najbliższy pn, wt, śr do 25-ego danego miesiąca
		$filter_arr['tel1'] = array("kontakt tel.", 'desc'=>array("", "Ostatni kontakt tel. ponad 3 m-ce temu"));
		$filter_arr['bad_address'] = array("błędny adres");
		$filter_arr['wezwanie1'] = array("wezwanie do zapłaty", 'desc'=>array("2 x FVat", "2 faktury"));
		$filter_arr['waiting-wezwanie2'] = array("oczekiwanie 7 dni od terminu płatności");
		$filter_arr['wezwanie2'] = array("ostateczne wezwanie do zapłaty, rozwiązanie umowy", 'desc'=>array("wezwanie + 14", "wezwanie + 14 dni"));
		$filter_arr['waiting-krd'] = array("min. 30 dni od ostatecznego wezwania");
		$filter_arr['krd'] = array("do przekazania do KRD", 'desc'=>array("wezwanie ost. + 60", "wezwanie ost. + 60 dni"));
		$filter_arr['waiting-sad'] = array("przekazano do KRD");
		$filter_arr['docs-in-sad'] = array("przekazać sprawę do sądu");// waiting-sad = 30 dni
		$filter_arr['sad'] = array("przekazano sprawę do sądu");
		$filter_arr['3 m-ce przed'] = array("3 m-ce przed przedawnieniem");// 3 m-ce przed przedawnieniem
		$filter_arr['po-terminie'] = array("po terminie");// 3 lata po dacie platnosci faktur
		$filter_arr['has_nr_sad'] = array("nr sprawy sąd");
		$filter_arr['has_nr_komornik'] = array("nr sprawy komornik");
		$filter_arr['has_ustalenia'] = array("ustalenia z klientem");
		$filter_arr['sad_and_komornik'] = array("sąd z komornik");
		$filter_arr['sad_bez_komornik'] = array("sąd bez komornik");
		$filter_arr['isMovedToVectra'] = array("przeniesieni do Vectra");
		return $filter_arr;
	}

	public static function get_by_user(&$user) {
		return DB::getPDO()->fetchFirstAsObject("
			select *
			from `USERS2_WINDYKACJA_STATUS` as w
			where w.`ID_BILLING_USERS` = :id_user
		", [ ':id_user' => $user->ID ]);
	}

	/**
	 * Auto update user stats.
	 * 
	 * @returns boolean - status changed or not
	 * @param $user - object return from function Windykacja_StatsModel::get_user_by_id
	 * @param $billing_docs - user billing docs
	 * 
	 * @used in task_update_stats - for all users
	 * @used in Windykacja_View::user_historia_platnosci after view billing docs
	 * 
	 * save HIST at status change to WAITING
	 */
	public static function update_stats(&$user, &$billing_docs) {
		$data_arr = array();
		if (!V::get('DBG_LAST_FVAT_PAY_TERM', '', $_GET)) {
			if ($user->A_STATUS_UPDATE_DATE >= date("Y-m-d")) {
				return;
			}
		}
		self::updateUserKoresp($user);

		$saldo = $billing_docs->get_saldo();
		$data_arr["A_STATUS_UPDATE_DATE"] = date("Y-m-d");
		$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
		$data_arr["A_RECORD_UPDATE_AUTHOR"] = "stat-update";

		$company = Windykacja_StatsModel::getOwnerCompany($user->ID_BILLING_USERS);
		$data_arr["ACCOUNT_NUMBER"] = Windykacja_FunkcjeL1::bankowy_make_nrach($company['NR_RACH_MASS_PAY'], $user->ID_BILLING_USERS, 0);
		$windykPerms = DB::getPDO()->fetchFirst(" select w.ID, w.A_ADM_COMPANY, w.A_CLASSIFIED from USERS2_WINDYKACJA_STATUS w where w.ID_BILLING_USERS = :id ", [ ':id' => $user->ID_BILLING_USERS ]);
		$companyPerms = DB::getPDO()->fetchFirst(" select c.ID, c.A_ADM_COMPANY, c.A_CLASSIFIED from COMPANIES c where c.ID = :id ", [ ':id' => $user->ID_BILLING_USERS ]);
		DBG::log([ 'windykPerms' => $windykPerms, 'companyPerms' => $companyPerms ], 'array', 'TODO:DBG:user perm from COMPANIES table');
		if ($windykPerms && $companyPerms) {
			if ($companyPerms['A_ADM_COMPANY'] != $windykPerms['A_ADM_COMPANY']) $data_arr['A_ADM_COMPANY'] = $companyPerms['A_ADM_COMPANY'];
			if ($companyPerms['A_CLASSIFIED'] != $windykPerms['A_CLASSIFIED']) $data_arr['A_CLASSIFIED'] = $companyPerms['A_CLASSIFIED'];
		}
		$data_arr["SERVICES_STREETS"] = Windykacja_StatsModel::getAllServicesStreetsCSV($user);
		$data_arr["BILLING_USER_NAME"] = DB::getPDO()->fetchValue("
			select concat(bua.`P_NAME`, ' ', bua.`P_NAME_SECOND`) as P_NAME
			from `BILLING_USERS_ADD` as bua
				left join `BILLING_USERS` as bu on ( bu.`ID` = bua.`id_users` )
			where bua.`id_users` = :id_user
		", [ ':id_user' => $user->ID_BILLING_USERS ]);
		$data_arr["BILLING_USER_ADRES"] = DB::getPDO()->fetchValue("
			select concat(bua.P_ADDRESS_POST_CODE, ' ', bua.P_ADDRESS_CITY, ' ', bua.P_ADDRESS_STREET, ' ', bua.P_ADDRESS_HOME, '/', bua.P_ADDRESS_HOUSE) as P_NAME
			from `BILLING_USERS_ADD` as bua
				left join `BILLING_USERS` as bu on ( bu.`ID` = bua.`id_users` )
			where bua.`id_users` = :id_user
		", [ ':id_user' => $user->ID_BILLING_USERS ]);
		$data_arr["BILLING_OWNER_NAME"] = DB::getPDO()->fetchValue("
			select ( select bo.name1 from BILLING_OWNER bo where bo.ID = bu.BILLING_OWNER ) as BILLING_OWNER_NAME
			from `BILLING_USERS` as bu
			where bu.ID = :id_user
		", [ ':id_user' => $user->ID_BILLING_USERS ]);
		// set default values
		//$data_arr["A_STATUS"] = "WAITING";
		//$data_arr["PAY_FVAT"] = 0;
		//$data_arr["PAY_DATE"] = "0000-00-00";
		// update values
		{// LAST_PAY_DATE
			$lastPayDoc = $billing_docs->get_last_pay_doc();
			if ($lastPayDoc) {
				$data_arr["LAST_PAY_DATE"] = $lastPayDoc->get('BILL_DATE');
				$data_arr["LAST_PAY_VALUE"] = $lastPayDoc->get('MA');
				if(V::get('DBG_LAST_FVAT_PAY_TERM', '', $_GET)){echo'<pre>lastPayDoc: ';print_r($lastPayDoc);echo'</pre>';}
			}
		}
		$data_arr["PAY_SALDO"] = $saldo;
		$data_arr["PAY_SALDO_30_DNI"] = $billing_docs->get_saldo_30_dni();
		$data_arr["PAY_SALDO_ISSUED"] = $billing_docs->get_saldo_issued();
		$data_arr["PAY_FVAT"] = 0;
		$data_arr["PAY_DATE_FIRST_FVAT"] = '0000-00-00';
		$lastFvatDoc = $billing_docs->get_last_fvat_doc();
		if(V::get('DBG_LAST_FVAT_PAY_TERM', '', $_GET)){echo'<pre>lastFvatDoc: ';print_r($lastFvatDoc);echo'</pre>';}
		if ($lastFvatDoc) {
			$data_arr["LAST_FVAT_PAY_TERM"] = $lastFvatDoc->get('PAYMENT_TERM');
			$data_arr["LAST_FVAT_SELL_DATE"] = $lastFvatDoc->get('SELL_DATE');
			$data_arr["LAST_FVAT_VALUE"] = $lastFvatDoc->get('WINIEN');
		}
		if ($saldo <= -0.05) {
			$fvat_arr = $billing_docs->get_unpaid_fvat();
			$data_arr["PAY_FVAT"] = count($fvat_arr);
			if (count($fvat_arr) > 0) {
				foreach ($fvat_arr as $v_fvat) {
					if (!isset($data_arr["PAY_DATE"]) || $v_fvat['PAYMENT_TERM'] > $data_arr["PAY_DATE"]) {
						$data_arr["PAY_DATE"] = $v_fvat['PAYMENT_TERM'];
					}
					if ('0000-00-00' == $data_arr["PAY_DATE_FIRST_FVAT"] || $v_fvat['PAYMENT_TERM'] < $data_arr["PAY_DATE_FIRST_FVAT"]) {
						$data_arr["PAY_DATE_FIRST_FVAT"] = $v_fvat['PAYMENT_TERM'];
					}
				}
			}
		}

		// zmiana statusu na kolejny wg. salda
		switch ($user->A_STATUS) {
			case 'WAITING':// auto
				if ($user->wezwanie1_DATE == '0000-00-00') {
					if ($saldo <= -0.05) {
						$fvat_arr = $billing_docs->get_unpaid_fvat();
						if (count($fvat_arr) > 1) {
							$data_arr["A_STATUS"] = "wezwanie1";
						}
					}
				}
				else if ($user->wezwanie2_DATE == '0000-00-00') {
					$data_arr["A_STATUS"] = "waiting-wezwanie2";
				}
				else {
					$data_arr["A_STATUS"] = "wezwanie2";
				}
				break;

			case 'wezwanie1':// wymaga `PAY_TERM`, `wezwanie1_DATE`, `ID_KORESP`
				break;

			case 'waiting-wezwanie2':// auto -> wezwanie2
				if ($user->wezwanie2_DATE == '0000-00-00') {
					$payTermArr = explode('-', $user->PAY_TERM);// Y-m-d
					$payTermPlus7dni = date("Y-m-d", mktime(0,0,0, intval($payTermArr[1]), intval($payTermArr[2]) + 7, intval($payTermArr[0])));
					// TODO: data +7 dni od terminu płatności (PAY_TERM) / mktime ($user->PAY_TERM + 7 dni)
					if ($user->PAY_TERM != '0000-00-00' && $payTermPlus7dni < date('Y-m-d')) {
						$data_arr["A_STATUS"] = "wezwanie2";
					}
				}
				break;

			case 'wezwanie2':// wymaga `PAY_TERM`, `wezwanie2_DATE`, `ID_KORESP`
				break;

			case 'waiting-krd':// auto -> krd jesli minelo 30 dni od wystawienia wezwanie2
				if ($user->wezwanie2_DATE != '0000-00-00') {
					$wzw2 = new stdClass();
					$wzw2->Y = intval(substr($user->wezwanie2_DATE, 0, 4));
					$wzw2->m = intval(substr($user->wezwanie2_DATE, 5, 2));
					$wzw2->d = intval(substr($user->wezwanie2_DATE, 8, 2));
					$wzw2->plus_30 = date("Y-m-d", mktime(0,0,0, $wzw2->m, $wzw2->d + 30, $wzw2->Y));
					if ($wzw2->plus_30 < date("Y-m-d")) {
						$data_arr["A_STATUS"] = "krd";
					}
				}
				break;

			case 'waiting-sad':// auto -> sad jesli minelo 30 dni od wpisania do krd (wpis_w_krd_DATE)
				if ($user->wpis_w_krd_DATE != '0000-00-00') {
					$krdDate = new stdClass();
					$krdDate->Y = intval(substr($user->wpis_w_krd_DATE, 0, 4));
					$krdDate->m = intval(substr($user->wpis_w_krd_DATE, 5, 2));
					$krdDate->d = intval(substr($user->wpis_w_krd_DATE, 8, 2));
					$krdDate->plus_30 = date("Y-m-d", mktime(0,0,0, $krdDate->m, $krdDate->d + 30, $krdDate->Y));
					if ($krdDate->plus_30 < date("Y-m-d")) {
						$data_arr["A_STATUS"] = "docs-in-sad";
					}
				}
				break;

			default:
				
		}

		// zmiana statusu na czysty - WAITING
		if ($saldo > -0.05 || $data_arr["PAY_FVAT"] < 2) {
			if (!in_array($user->A_STATUS, array('waiting-sad', 'docs-in-sad', 'sad'))) {
				$data_arr["A_STATUS"] = "WAITING";
				$data_arr["wezwanie2_DATE"] = '0000-00-00';
				$data_arr["wezwanie1_DATE"] = '0000-00-00';
				//$data_arr["PAY_DATE"] = '0000-00-00';// TODO: ?
			}
		}

		{
			$hasActiveNET = 0;
			$hasActiveTV = 0;
			$isMovedToVectra = 0;
			$statsActiveVectra = DB::getPDO()->fetchFirst("
				select
					IF(1 = (
							select 1 as hasActiveNET
							from `SERVICES` srv
							where srv.`ID_BILLING_USERS` = :id_user
								and srv.`NAME_LIST_SERVICES`='USERS2'
								and 'NORMAL'=A_STATUS_L2_SQL_L1(srv.`ID`)
							limit 1
						), 1, 0) as hasActiveNET
					,
					IF(1 = (
							select 1 as hasActiveTV
							from `SERVICES` srv
							where srv.`ID_BILLING_USERS` = :id_user
								and srv.`NAME_LIST_SERVICES`='TV'
								and 'NORMAL'=A_STATUS_L2_SQL_L1(srv.`ID`)
							limit 1
						), 1, 0) as hasActiveTV
					,
					IF(1 = ('REZYGNACJA_Z_PRZEJSCIEM_DO_VECTRA'=(select `RODZAJ_DZIALANIA_HANDLOWEGO`
						from `DEALS_TABLE`
						where `A_STATUS`='NORMAL'
							and `ID_BILLING_USERS` = :id_user
						order by `ID` DESC
						limit 1)
						), 1, 0) as IS_MOVED_TO_VECTRA
			", [ ':id_user' => $user->ID ]);
			DBG::_('DBG_ACTIVE_SRV', '>2', "DBG_ACTIVE_SRV sql", $sql, __CLASS__, __FUNCTION__, __LINE__);
			if ($statsActiveVectra) {
				$hasActiveNET = V::get('hasActiveNET', '', $statsActiveVectra);
				$hasActiveTV  = V::get('hasActiveTV', '', $statsActiveVectra);
				$isMovedToVectra = V::get('IS_MOVED_TO_VECTRA', '', $statsActiveVectra);
			}
			$data_arr['HAS_ACTIVE_NET'] = $hasActiveNET;
			$data_arr['HAS_ACTIVE_TV']  = $hasActiveTV;
			$data_arr['IS_MOVED_TO_VECTRA'] = $isMovedToVectra;
		}
		if(V::get('DBG_LAST_FVAT_PAY_TERM', '', $_GET)){echo'<pre>data_arr #' . __LINE__ . ': ';print_r($data_arr);echo'</pre>';}

		DB::getPDO()->update('USERS2_WINDYKACJA_STATUS', 'ID', $user->WINDYKACJA_ID, $data_arr);

		// update HIST - tylko zmiana na WAITING - powrót do oczekujących
		if (isset($data_arr["A_STATUS"]) && $data_arr["A_STATUS"] == "WAITING" && $user->A_STATUS != "WAITING") {
			DB::getPDO()->insert('USERS2_WINDYKACJA_STATUS_HIST', array_merge($data_arr, [
				'ID_USERS2' => $user->WINDYKACJA_ID,
			]));
		}
	}

	public static function updateUserKoresp($user) {
		DB::getPDO()->execSql("
			insert into `USERS2_WINDYKACJA_STATUS_HIST` (
					`A_RECORD_UPDATE_DATE`
					, `A_RECORD_UPDATE_AUTHOR`
					, `ID_USERS2`
					, `ID_KORESP`
					, `A_STATUS_UPDATE_DATE`
				)
				select
					NOW() as `A_RECORD_UPDATE_DATE`
					, 'auto-update-koresp-hist' as `A_RECORD_UPDATE_AUTHOR`
					, w.`ID` as `ID_USERS2`
					, k.`ID` as `ID_KORESP`
					, k.`K_DATA_OTRZYM_KORESP` as `A_STATUS_UPDATE_DATE`
				from `USERS2_WINDYKACJA_STATUS` w
					join `IN7_DZIENNIK_KORESP` k on (k.`ID_BILLING_USERS`=w.`ID_BILLING_USERS`)
					left join `USERS2_WINDYKACJA_STATUS_HIST` h on (h.`ID_USERS2`=w.`ID` and h.`ID_KORESP`=k.`ID`)
				where 1=1
					and h.ID is null
					and w.`ID_BILLING_USERS` = :id_user
		", [ ':id_user' => $user->ID_BILLING_USERS ]);
	}

	public static function &get_phone_status_array(&$user) {
		$ret = array();
		$status_info = self::get_phone_status_info($user);
		foreach ($status_info as $k => $v) {
			$ret[$k] = $v['label'];
		}
		return $ret;
	}

	/**
	 * @param $user
	 * 
	 * @returns array of 'label', 'date' -> nowy PAY_TERM ustalony wg. aktualnego stanu
	 */
	public static function get_phone_status_info($user) {
		$ret = array();
		$ret['nie_zaplaci'] = array('label'=>"nie zapłaci", 'date'=>'');
		$today = date("Y-m-d");
		$date = date("Y-m-d");
		{// PAY_TERM jesli jest ustalony np. z wewaznie1 lub wezwanie2
			if ($user->PAY_TERM != '0000-00-00') {
				if ($user->PAY_TERM > $today) {
					// jest już ustalona data platnosci na przyszłość
					$date = $user->PAY_TERM;
				}
			}
		}

		if ($date > $today) {// data platnosci w przyszlosci -> ok
			{// zapłaci w terminie today +14 dni lub PAY_TERM jesli jest ustalony np. z wewaznie1 lub wezwanie2
				$ret['zaplaci_w_terminie'] = array('label'=>"zapłaci w terminie", 'date'=>$date);
			}
		}
		else {// data platnosci w przeszlosci - przeterminowana -> ustalic nowa (+14,+1mc,+2mc,+3mc)
			{// zapłaci w terminie today +14 dni lub PAY_TERM jesli jest ustalony np. z wewaznie1 lub wezwanie2
				$date = date("Y-m-d", mktime(0,0,0,date('m'), date('d') + 14, date('Y')));
				$ret['zaplaci_w_terminie'] = array('label'=>"zapłaci w terminie", 'date'=>$date);
			}
			{// zaplaci za 1-mc pozniej
				$date = date("Y-m-d", mktime(0,0,0,date('m') + 1, date('d'), date('Y')));
				$ret['zaplaci_za_1mc'] = array('label'=>"zapłaci miesiąc później", 'date'=>$date);
			}
			{// zaplaci za 2-mce pozniej
				$date = date("Y-m-d", mktime(0,0,0,date('m') + 2, date('d'), date('Y')));
				$ret['zaplaci_za_2mc'] = array('label'=>"zapłaci 2 miesiące później", 'date'=>$date);
			}
			{// zaplaci za 3-mce pozniej
				$date = date("Y-m-d", mktime(0,0,0,date('m') + 3, date('d'), date('Y')));
				$ret['zaplaci_za_3mc'] = array('label'=>"zapłaci 3 miesiące później", 'date'=>$date);
			}
		}

		$ret['nie_odbiera'] = array('label'=>"nie obiera lub wyłączony telefon", 'date'=>'');
		$ret['zly_numer'] = array('label'=>"zły numer telefonu", 'date'=>'');
		return $ret;
	}

	public static function get_sms_status_info($user, $terminPlatnosci = '') {
		$ret = array();
		$today = date("Y-m-d");
		$due_date = $user->PAY_TERM;// ustalowny pay term
		$zaleglosc = number_format($user->PAY_SALDO, 2, ',', '');
		if ($user->PAY_SALDO <= -0.05) {
			$msg = "Twoje saldo na dzien {$today} wynosi {$zaleglosc} zl.\n";
			if (strlen($terminPlatnosci) == 10 && $terminPlatnosci > date("Y-m-d")) {
				$msg .= "Prosimy o uregulowanie w/w zaleglosci w terminie do {$terminPlatnosci}.";
			} else {
				$msg .= "Prosimy o niezwloczne uregulowanie zaleglosci.";
			}
			if ($due_date > $today) {
			//	$msg .= "Prosimy o uregulowanie zaleglosci do dnia {$due_date} r.";
			} else {
			}
			$ret['Powiadomienie SMS o zaleglosciach'] = array('label'=>"Windykacja: Powiadomienie SMS o zaleglosciach", 'msg'=>$msg);
			// Z powodu niedotrzymania terminu płatności nastąpi blokada usług. Dowód wpłaty w kwocie #Saldo# zł prosimy przesłać na  bok@biall.net.pl. Szczegóły 587277777.
			$doZaplaty = number_format(-1 * $user->PAY_SALDO, 2, ',', '');
			$msgBlokada = "Z powodu niedotrzymania terminu platnosci nastapi blokada uslug. Dowod wplaty w kwocie {$doZaplaty} zl prosimy przeslac na bok@biall.net.pl. Szczegoly 587277777.";
			$ret['Powiadomienie SMS o blokadzie'] = array('label'=>"Windykacja: Powiadomienie SMS o blokadzie", 'msg'=>$msgBlokada);
		}
		return $ret;
	}

	/*
<html><body>Informujemy Pana/Pania o zalegosci w platnosciach w wysokosci 109.16 zl. Prosimy o uregulowanie w/w zaleglosci w terminie do 31.03.2015 r.<br>W przypadku watpliwosci prosimy o kontakt z Biurem Obslugi Klienta. <p>BIALL-NET Sp. z o.o. <br><br>Biuro Obslugi Klienta:<br>tel. 58 741 84 10<br>fax 58 741 84 30</body></html>
	*/
	public static function get_mail_status_info($user, $terminPlatnosci = '') {
		$ret = array();
		$today = date("Y-m-d");
		$due_date = $user->PAY_TERM;// ustalowny pay term
		$zaleglosc = number_format(abs($user->PAY_SALDO), 2, ',', '');
		if ($user->PAY_SALDO <= -0.05) {
			$msg = "Informujemy Pana/Pania o zalegosci w platnosciach w wysokosci {$zaleglosc} zl.\n";
			if (strlen($terminPlatnosci) == 10 && $terminPlatnosci > date("Y-m-d")) {
				$msg .= "Prosimy o uregulowanie w/w zaleglosci w terminie do {$terminPlatnosci}.";
			} else {
				$msg .= "Prosimy o niezwloczne uregulowanie zaleglosci.";
			}
			$msg .= "<br>W przypadku watpliwosci prosimy o kontakt z Biurem Obslugi Klienta.";
			$msg .= "<p>BIALL-NET Sp. z o.o.</p>";
			if ($due_date > $today) {
			//	$msg .= "Prosimy o uregulowanie zaleglosci do dnia {$due_date} r.";
			} else {
			}
			$ret['Powiadomienie o zaleglosciach'] = array('label'=>"Windykacja: Powiadomienie o zaleglosciach", 'msg'=>$msg);
		}
		return $ret;
	}

	/**
	 * Update user phone status.
	 * 
	 * @returns boolean - status changed or not
	 * @param $user - object return from function Windykacja_StatsModel::get_user_by_id
	 * @param $phone_status - phone status
	 * 
	 */
	public static function update_phone_status($user, $phone_status) {
		$data_arr = array();

		$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
		$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
		$data_arr["LAST_PHONE_STATUS"] = $phone_status;
		$data_arr["LAST_PHONE_STATUS_DATE"] = date("Y-m-d");
		$status_info = self::get_phone_status_info($user);
		if (array_key_exists($phone_status, $status_info)) {
			$date = V::get('date', '', $status_info[$phone_status]);
			if ($date != '' && $date != '0000-00-00') {
				$data_arr["PAY_TERM"] = $date;
			}
		}

		DB::getPDO()->update('USERS2_WINDYKACJA_STATUS', 'ID', $user->WINDYKACJA_ID, $data_arr);
		DB::getPDO()->insert('USERS2_WINDYKACJA_STATUS_HIST', array_merge($data_arr, [
			'ID_USERS2' => $user->WINDYKACJA_ID,
		]));

		return true;
	}

	public static function update_sms_status($user, $sms_status) {
		$data_arr = array();
		$status_info = self::get_sms_status_info($user);
		//echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">$sms_status ';print_r($sms_status);echo'</pre>';
		//echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">$status_info ';print_r($status_info);echo'</pre>';

		if (!array_key_exists($sms_status, $status_info)) {
			echo '<div class="alert alert-danger">Nieznany status!</div>';
			return false;
		}
		$db_webone = DB::getPDO('931');
		if (!$db_webone) {
			echo '<div class="alert alert-danger">Brak połączenia do bazy billing!</div>';
			return false;
		}
		$msgId = $db_webone->insert('HIST_CONTACTS', [
			'ID_BILLING_USERS' => $user->ID,
			'SUBJECT' => $status_info[$sms_status]['label'],
			'BODY_HTML' => $status_info[$sms_status]['msg'],
			'REQUEST_STATUS_SMS' => "SENT_SMS",
		]);
		//echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">$msgId ';print_r($msgId);echo'</pre>';

		$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
		$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
		$data_arr["LAST_SMS_MSG_ID"] = $msgId;
		$data_arr["LAST_SMS_STATUS"] = $sms_status;
		$data_arr["LAST_PHONE_STATUS_DATE"] = date("Y-m-d");
		if (array_key_exists($sms_status, $status_info)) {
			$date = V::get('date', '', $status_info[$sms_status]);
			if ($date != '' && $date != '0000-00-00') {
				$data_arr["PAY_TERM"] = $date;
			}
		}
		DB::getPDO()->update('USERS2_WINDYKACJA_STATUS', 'ID', $user->WINDYKACJA_ID, $data_arr);
		DB::getPDO()->insert('USERS2_WINDYKACJA_STATUS_HIST', array_merge($data_arr, [
			'ID_USERS2' => $user->WINDYKACJA_ID,
		]));
		return true;
	}

	public static function update_mail_status($user, $mail_status) {
		$data_arr = array();
		$status_info = self::get_mail_status_info($user);
		//echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">$mail_status ';print_r($mail_status);echo'</pre>';
		//echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">$status_info ';print_r($status_info);echo'</pre>';

		if (!array_key_exists($mail_status, $status_info)) {
			echo '<div class="alert alert-danger">Nieznany status!</div>';
			return false;
		}
		$db_webone = DB::getPDO('931');
		if (!$db_webone) {
			echo '<div class="alert alert-danger">Brak połączenia do bazy billing!</div>';
			return false;
		}
		$msgId = $db_webone->insert('HIST_CONTACTS', [
			'ID_BILLING_USERS' => $user->ID,
			'SUBJECT' => $status_info[$mail_status]['label'],
			'BODY_HTML' => "<html><body>{$status_info[$mail_status]['msg']}</body></html>",
			'REQUEST_STATUS_MAIL' => "CONFIRM_SENT_MAIL",
		]);
		echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">$msgId ';print_r($msgId);echo'</pre>';

		$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
		$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
		$data_arr["LAST_MAIL_MSG_ID"] = $msgId;
		$data_arr["LAST_MAIL_STATUS"] = $mail_status;
		$data_arr["LAST_PHONE_STATUS_DATE"] = date("Y-m-d");
		if (array_key_exists($mail_status, $status_info)) {
			$date = V::get('date', '', $status_info[$mail_status]);
			if ($date != '' && $date != '0000-00-00') {
				$data_arr["PAY_TERM"] = $date;
			}
		}

		DB::getPDO()->update('USERS2_WINDYKACJA_STATUS', 'ID', $user->WINDYKACJA_ID, $data_arr);
		DB::getPDO()->insert('USERS2_WINDYKACJA_STATUS_HIST', array_merge($data_arr, [
			'ID_USERS2' => $user->WINDYKACJA_ID,
		]));
		return true;
	}

	public static function update_old_id_koresp(&$user, $id_koresp, $koresp_type) {
		$koresp = DB::getPDO()->fetchFirst("
			select *
			from IN7_DZIENNIK_KORESP
			where ID = :id
		", [ ':id' => $id_koresp ]);
		if (!$koresp) {
			return false;
		}

		// check if ID koresp already exists in HIST table!
		// $sql = "select wh.`ID`,wh.``,wh.`` from `USERS2_WINDYKACJA_STATUS_HIST` as wh where ";

		DB::getPDO()->insert('USERS2_WINDYKACJA_STATUS_HIST', [
			'ID_USERS2' => $user->WINDYKACJA_ID,
			'A_RECORD_UPDATE_DATE' => date("Y-m-d-H:i"),
			'A_RECORD_UPDATE_AUTHOR' => $_SESSION['ADM_ACCOUNT'],
			'ID_KORESP' => $id_koresp,
			//'A_STATUS_UPDATE_DATE' => $koresp['K_DATA_OTRZYMANEJ_KORESP'], // data utworzenia
			'A_STATUS_UPDATE_DATE' => $koresp['K_DATA_OTRZYM_KORESP'], // data wyslania
			// $koresp_type ?
		]);
		return true;
	}

	/**
	 * @returns int - ID Koresp or null if error
	 */
	public static function update_bok_rozwiazanie_umowy(&$user, $id_proj, $nr_umowy, $powod, $powod_desc, $termin_odlaczenia, &$msg_log) {
		$data_arr = array();
		if ($id_proj <= 0 || $nr_umowy <= 0 || $powod = '') {
			return null;
		}
		$new_id_deals = Windykacja_StatsModel::create_deals_rozwiazanie($user->ID, $nr_umowy, $termin_odlaczenia, $powod_desc);
		if (!$new_id_deals) {
			$msg_log[] = "Nie udało się utworzyć rekordu w DEALS_TABLE";
			return null;
		}
		$msg_log[] = "Utworzonno rekord w DEALS_TABLE ID={$new_id_deals}";

		$new_id_koresp = Windykacja_StatsModel::create_koresp($user, 'rozwiazanie umowy', $id_proj, array('nr_umowy'=>$nr_umowy, 'powod'=>$powod, 'powod_desc'=>$powod_desc));
		if (!$new_id_koresp) {
			$msg_log[] = "Nie udało się utworzyć rekordu KORESP";
			return null;
		}
		$msg_log[] = "Utworzonno rekord KORESP ID={$new_id_koresp}";
		$data_arr["ID_KORESP"] = $new_id_koresp;
		$data_arr["params"] = "rozwiazanie umowy,{$nr_umowy},{$powod},{$termin_odlaczenia},{$new_id_deals}";
		$data_arr["A_STATUS_UPDATE_DATE"] = date("Y-m-d");// zawsze podana jesli jest zmiana A_STATUS

		$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
		$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
		DB::getPDO()->update('USERS2_WINDYKACJA_STATUS', 'ID', $user->WINDYKACJA_ID, $data_arr);

		// update $user data
		foreach ($data_arr as $k => $v) {
			$user->$k = $v;
		}

		DB::getPDO()->insert('USERS2_WINDYKACJA_STATUS_HIST', array_merge($data_arr, [ 'ID_USERS2' => $user->WINDYKACJA_ID ]));

		return $new_id_koresp;
	}

	public static function update_users_table() {
		if (!isset($_SESSION['USERS2_WINDYKACJA_PANEL'])) $_SESSION['USERS2_WINDYKACJA_PANEL'] = array();
		$_SESSION['USERS2_WINDYKACJA_PANEL']['_initialized'] = 0;// always actualize
		if (0 == V::get('_initialized', 0, $_SESSION['USERS2_WINDYKACJA_PANEL'], 'int')) {
			DB::getPDO()->execSql("
				insert ignore into `USERS2_WINDYKACJA_STATUS` (`ID_BILLING_USERS`, `A_RECORD_CREATE_DATE`, `A_RECORD_CREATE_AUTHOR`)
				select `id_users`, :today , 'sync-users' from `BILLING_USERS_ADD`
			", [ ':today' => date("Y-m-d-H:i") ]);
			$_SESSION['USERS2_WINDYKACJA_PANEL']['_initialized'] = 1;
		}
	}

	public static function _to_update_where() {
		$sql_where = "";
		$sql_where_and_arr = array();
		//$sql_where_and_arr[] = "w.`A_STATUS`='WAITING'";
		$sql_where_and_arr[] = "w.`A_STATUS_UPDATE_DATE`<CURDATE()";
		$sql_where = implode(" and ", $sql_where_and_arr);
		return $sql_where;
	}

	public static function get_to_update_total() {
		$sql_where = self::_to_update_where();
		return DB::getPDO()->fetchValue("
			select count(1) as cnt
			from `USERS2_WINDYKACJA_STATUS` as w
			where {$sql_where}
		");
	}

	public static function get_to_update_list($limit) {
		$sql_where = self::_to_update_where();
		$sql_limit = ($limit > 0)? "limit {$limit}" : "";
		return array_map(function ($item) { return (object)$item; }, DB::getPDO()->fetchAll("
			select w.*
			from `USERS2_WINDYKACJA_STATUS` as w
			where {$sql_where}
			order by ID desc
			{$sql_limit}
		", [ ':id' => $user->WINDYKACJA_ID ]));
	}

	/**
	 * @returns boolean - status changed or not
	 * @param $user - object return from function Windykacja_StatsModel::get_user_by_id
	 * @param $data - array of new user data
	 *   1. wysłanie 1 wezwania do zapłaty ($user->A_STATUS='wezwanie1'; `PAY_TERM`, `wezwanie1_DATE`, `ID_PROJ`)
	 *   2. wysłanie 2 wezwania do zapłaty ($user->A_STATUS='wezwanie2'; `PAY_TERM`, `wezwanie2_DATE`, `ID_KORESP`)
	 *   3. klient spłacił wszystkie zobowiązania (`PAY_SALDO` >= -0.05)
	 * TODO: 4. bledny adres klienta - ponowne wyslanie wezwania - niezaleznie od statusu?
	 * 
	 * if $user->A_STATUS == 'wezwanie1' @param $data:
	 *   $data['wezwanie1_DATE'] - user data
	 *   $data['PAY_TERM'] - user data
	 *   $data['ID_PROJ'] - do Windykacja_StatsModel::create_koresp
	 */
	public static function update_user($user, $data) {
		if (empty($data)) {
			return $user;
		}
		$data_arr = array();
		// set up status by current status and $data
		switch ($user->A_STATUS) {
			case 'WAITING':// auto
				break;

			case 'wezwanie1':// wymaga `PAY_TERM`, `wezwanie1_DATE`, `ID_KORESP` - wysłanie wezwanie1 (z podaniem ID_KORESP, i PAY_TERM=NOW()+14dni)
				// TODO: ID_KORESP z utworzonego rekordu KORESP (wymaga ID_PROJ)
				if ("" != V::get('wezwanie1_DATE' ,'', $data) && "" != V::get('PAY_TERM' ,'', $data) && V::get('ID_PROJ' ,'', $data, 'int') > 0) {
					$new_id_koresp = Windykacja_StatsModel::create_koresp($user, 'wezwanie1', V::get('ID_PROJ' ,'', $data, 'int'));
					if (!$new_id_koresp) {
						// TODO: revert changes - DB error
						return $user;
					}
					$data_arr["ID_KORESP"] = $new_id_koresp;
					$data_arr["LAST_ID_KORESP_WEZWANIE1"] = $new_id_koresp;
					$data_arr["A_STATUS"] = "waiting-wezwanie2";
					$data_arr["wezwanie1_DATE"] = V::get('wezwanie1_DATE' ,'', $data);
					$data_arr["PAY_TERM"] = V::get('PAY_TERM' ,'', $data);
					unset($data['ID_PROJ']);// TODO: RM after mv $data to $data_arr
				}
				break;

			case 'waiting-wezwanie2':// auto
				break;

			case 'wezwanie2':// wymaga `PAY_TERM`, `wezwanie2_DATE`, `ID_KORESP` - wysłanie wezwanie2 (z podaniem ID_KORESP, i PAY_TERM=NOW()+14dni)
				if ("" != V::get('wezwanie2_DATE' ,'', $data) && "" != V::get('PAY_TERM' ,'', $data) && V::get('ID_PROJ' ,'', $data, 'int') > 0) {
					$new_id_koresp = Windykacja_StatsModel::create_koresp($user, 'wezwanie2', V::get('ID_PROJ' ,'', $data, 'int'));
					if (!$new_id_koresp) {
						// TODO: revert changes - DB error
						return $user;
					}
					$data_arr["ID_KORESP"] = $new_id_koresp;
					$data_arr["LAST_ID_KORESP_WEZWANIE2"] = $new_id_koresp;
					$data_arr["A_STATUS"] = "waiting-krd";
					$data_arr["wezwanie2_DATE"] = V::get('wezwanie2_DATE' ,'', $data);
					$data_arr["PAY_TERM"] = V::get('PAY_TERM' ,'', $data);
				}
				break;

			default:
				
		}

		// if status not set, check id user saldo is ok
		if (!isset($data_arr["A_STATUS"])) {
			if (isset($data['PAY_SALDO']) && $data['PAY_SALDO'] >= -0.05) {
				$data_arr["A_STATUS"] = "WAITING";
			}
		}

		// error jesli nie ustalono statusu
		if (!isset($data_arr["A_STATUS"])) {
			return false;
		}

		$data_arr["A_STATUS_UPDATE_DATE"] = date("Y-m-d");// zawsze podana jesli jest zmiana A_STATUS

		$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
		$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
		DB::getPDO()->update('USERS2_WINDYKACJA_STATUS', 'ID', $user->WINDYKACJA_ID, $data_arr);

		// update $user data
		foreach ($data_arr as $k => $v) {
			$user->$k = $v;
		}

		DB::getPDO()->insert('USERS2_WINDYKACJA_STATUS_HIST', array_merge($data_arr, [ 'ID_USERS2' => $user->WINDYKACJA_ID ]));

		return true;
	}

	public static function bad_address_save($user, $id_koresp) {
		$data_arr = array();
		$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
		$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
		$data_arr["BAD_ADDRESS"] = 1;
		$data_arr["ID_KORESP"] = $id_koresp;
		return self::_sql_update($user, $data_arr);
	}

	public static function bad_address_confirm($user, $id_koresp) {
		$data_arr = array();
		$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
		$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
		$data_arr["BAD_ADDRESS"] = 0;
		$data_arr["ID_KORESP"] = $id_koresp;
		return self::_sql_update($user, $data_arr);
	}

	public static function _sql_update($user, $data_arr) {
		DB::getPDO()->update('USERS2_WINDYKACJA_STATUS', 'ID', $user->WINDYKACJA_ID, $data_arr);

		// update $user data
		foreach ($data_arr as $k => $v) {
			$user->$k = $v;
		}

		DB::getPDO()->insert('USERS2_WINDYKACJA_STATUS_HIST', array_merge($data_arr, [ 'ID_USERS2' => $user->WINDYKACJA_ID ]));

		return true;
	}

	public static function change_status_save($user, $new_status) {
		$data_arr = array();
		$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
		$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
		$data_arr["A_STATUS_UPDATE_DATE"] = date("Y-m-d");
		$data_arr["A_STATUS"] = $new_status;
		if ($user->A_STATUS == 'krd' && $new_status == 'waiting-sad') {
			$data_arr["wpis_w_krd_DATE"] = date('Y-m-d');
		}
		return self::_sql_update($user, $data_arr);
	}

	public static function nr_sprawy_krd_save($user, $nr_sprawy_krd) {
		$data_arr = array();
		$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
		$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
		$data_arr["NR_SPRAWY_KRD"] = $nr_sprawy_krd;
		return self::_sql_update($user, $data_arr);
	}

	public static function nr_sprawy_sad_save($user, $nr_sprawy_sad) {
		$data_arr = array();
		$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
		$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
		$data_arr["NR_SPRAWY_SAD"] = $nr_sprawy_sad;
		return self::_sql_update($user, $data_arr);
	}

	public static function nr_sprawy_komornik_save($user, $nr_sprawy_komornik) {
		$data_arr = array();
		$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
		$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
		$data_arr["NR_SPRAWY_KOMORNIK"] = $nr_sprawy_komornik;
		return self::_sql_update($user, $data_arr);
	}

	public static function ustalenia_info_save($user, $ustalenia_date, $ustalenia_info) {
		$data_arr = array();
		$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
		$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
		$data_arr["L_APPOITMENT_DATE"] = $ustalenia_date;
		$data_arr["L_APPOITMENT_INFO"] = $ustalenia_info;
		$data_arr["L_APPOITMENT_USER"] = $_SESSION['ADM_ACCOUNT'];

		if (empty($ustalenia_info) && (empty($ustalenia_date) || $ustalenia_date == '0000-00-00')) {
			$data_arr["L_APPOITMENT_USER"] = '';
		}
		return self::_sql_update($user, $data_arr);
	}

	public static function getUserHistStatusEvents($idUser) { // TODO: convert to idUser. Sql join USERS2_WINDYKACJA_STATUS w where w.ID_BILLING_USERS = :id_user
		return DB::getPDO()->fetchAll("
			select wh.*, kor.`K_ZAWARTOS`
			from `USERS2_WINDYKACJA_STATUS` as w
				join `USERS2_WINDYKACJA_STATUS_HIST` as wh on ( wh.`ID_USERS2` = w.ID )
				left join `IN7_DZIENNIK_KORESP` as kor on ( wh.`ID_KORESP` != 'N/S;' and kor.`ID` = wh.`ID_KORESP` )
			where w.`ID_BILLING_USERS` = :id
				and wh.`A_STATUS_UPDATE_DATE` != 'N/S;'
		", [ ':id' => $idUser ]);
	}

	public static function getUserHistPhoneEvents($idUser) {
		return DB::getPDO()->fetchAll("
			select wh.*
			from `USERS2_WINDYKACJA_STATUS` as w
				join `USERS2_WINDYKACJA_STATUS_HIST` as wh on ( wh.`ID_USERS2` = w.ID )
			where w.`ID_BILLING_USERS` = :id
				and wh.`LAST_PHONE_STATUS_DATE` != 'N/S;'
		", [ ':id' => $idUser ]);
	}

	public static function getUserBadAddressEvents($idUser) {
		return DB::getPDO()->fetchAll("
			select wh.*
			from `USERS2_WINDYKACJA_STATUS` as w
				join `USERS2_WINDYKACJA_STATUS_HIST` as wh on ( wh.`ID_USERS2` = w.ID )
			where w.`ID_BILLING_USERS` = :id
				and wh.`BAD_ADDRESS` != 'N/S;'
			order by wh.`ID` ASC
		", [ ':id' => $idUser ]);
	}

	public static function get_status_count() {
		$sql_where_and_arr = array();
		$usrAclGroups = User::getLdapGroupsNames();
		$usrAclGroups[] = '';
		$sqlUsrAclGroups = "'" . implode("','", $usrAclGroups) . "'";
		$sql_where_and_arr[] = "a.`A_ADM_COMPANY` in({$sqlUsrAclGroups})";
		$sql_where_and_arr[] = "a.`A_CLASSIFIED` in({$sqlUsrAclGroups})";
		$sqlWhereAdd = " and " . implode(" and ", $sql_where_and_arr);

		$ret = array();
		$ret = array_map(function ($item) {
			return (object)$item;
		}, DB::getPDO()->fetchAllByKey("
			select w.`A_STATUS`, count(1) as cnt
				, sum(IF(w.`PAY_SALDO`<=-0.05, w.`PAY_SALDO`, 0)) as suma_zaleglosci
			from  `USERS2_WINDYKACJA_STATUS` as w
				left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
			where 1=1 {$sqlWhereAdd}
			group by w.`A_STATUS`
		", $key = 'A_STATUS'));
		$ret['po-terminie'] = DB::getPDO()->fetchFirstAsObject("
			select w.`A_STATUS`
				, count(1) as cnt
				, sum(IF(w.`PAY_SALDO`<=-0.05, w.`PAY_SALDO`, 0)) as suma_zaleglosci
			from  `USERS2_WINDYKACJA_STATUS` as w
				left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
			where
				w.`PAY_SALDO`<=-0.05
				and w.`PAY_DATE`!='0000-00-00'
				and w.`PAY_DATE`<DATE_SUB(NOW(), INTERVAL 36 MONTH)
				{$sqlWhereAdd}
		");
		$ret['3 m-ce przed'] = DB::getPDO()->fetchFirstAsObject("
			select w.`A_STATUS`
				, count(1) as cnt
				, sum(IF(w.`PAY_SALDO`<=-0.05, w.`PAY_SALDO`, 0)) as suma_zaleglosci
			from  `USERS2_WINDYKACJA_STATUS` as w
				left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
			where
				w.`PAY_SALDO`<=-0.05
				and w.`PAY_DATE`!='0000-00-00'
				and w.`PAY_DATE`<DATE_SUB(NOW(), INTERVAL 33 MONTH)
				and w.`PAY_DATE`>DATE_SUB(NOW(), INTERVAL 36 MONTH)
				{$sqlWhereAdd}
		");
		$ret['tel1'] = DB::getPDO()->fetchFirstAsObject("
			select w.`A_STATUS`
				, count(1) as cnt
				, sum(IF(w.`PAY_SALDO`<=-0.05, w.`PAY_SALDO`, 0)) as suma_zaleglosci
			from  `USERS2_WINDYKACJA_STATUS` as w
				left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
			where
				w.`PAY_SALDO`<=-0.05
				and w.`A_STATUS` in('wezwanie1', 'waiting-wezwanie2', 'wezwanie2', 'waiting-krd')
				and ( w.`LAST_PHONE_STATUS_DATE`='0000-00-00'
					or w.`LAST_PHONE_STATUS_DATE`<DATE_SUB(NOW(), INTERVAL 14 DAY) )
				{$sqlWhereAdd}
		");
		$ret['bad_address'] = DB::getPDO()->fetchFirstAsObject("
			select w.`A_STATUS`
				, count(1) as cnt
				, sum(IF(w.`PAY_SALDO`<=-0.05, w.`PAY_SALDO`, 0)) as suma_zaleglosci
			from  `USERS2_WINDYKACJA_STATUS` as w
				left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
			where
				w.`PAY_SALDO`<=-0.05
				-- and w.`A_STATUS` in('wezwanie1', 'waiting-wezwanie2', 'wezwanie2', 'waiting-krd')
				and w.`BAD_ADDRESS`>0
				{$sqlWhereAdd}
		");

		$stanZeroLimit = -0.05;

		$statsZero10ego = DB::getPDO()->fetchFirstAsObject("
			select w.`A_STATUS`
				, count(1) as cnt
				, sum(IF(w.`PAY_SALDO`>{$stanZeroLimit}, 1, 0)) as cnt_stan_zero
				, sum(IF(w.`PAY_SALDO`<={$stanZeroLimit}, 1, 0)) as cnt_stan_minus
				, sum(IF(w.`PAY_SALDO`<={$stanZeroLimit}, w.`PAY_SALDO`, 0)) as suma_zaleglosci
			from  `USERS2_WINDYKACJA_STATUS` as w
				left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
			where
				w.`A_STATUS`='WAITING'
				{$sqlWhereAdd}
		");
		if ($statsZero10ego) {
			$ret['stan_zero'] = (object)[ 'A_STATUS' => 'stan_zero', 'cnt' => $statsZero10ego->cnt_stan_zero, 'suma_zaleglosci' => 0 ];
			$ret['10-ego']    = (object)[ 'A_STATUS' => '10-ego',    'cnt' => $statsZero10ego->cnt_stan_minus, 'suma_zaleglosci' => $statsZero10ego->suma_zaleglosci ];
		}
		// filter: 'has_nr_sad', 'has_nr_komornik', 'has_ustalenia'
		$statsSad = DB::getPDO()->fetchFirstAsObject("
			select w.`A_STATUS`
				, count(1) as cnt
				, sum(IF(w.`NR_SPRAWY_SAD`!='', 1, 0)) as cnt_sad
				, sum(IF(w.`NR_SPRAWY_KOMORNIK`!='', 1, 0)) as cnt_komornik
				, sum(IF(w.`L_APPOITMENT_INFO`!='', 1, 0)) as cnt_ustalenia
			from  `USERS2_WINDYKACJA_STATUS` as w
				left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
			where
				(w.`NR_SPRAWY_SAD`!=''
					or w.`NR_SPRAWY_KOMORNIK`!=''
					or w.`L_APPOITMENT_INFO`!=''
				)
				{$sqlWhereAdd}
		");
		if ($statsSad) {
			$ret['has_nr_sad']      = (object)[ 'A_STATUS' => 'has_nr_sad',      'cnt' => $statsSad->cnt_sad,       'suma_zaleglosci' => null ];
			$ret['has_nr_komornik'] = (object)[ 'A_STATUS' => 'has_nr_komornik', 'cnt' => $statsSad->cnt_komornik,  'suma_zaleglosci' => null ];
			$ret['has_ustalenia']   = (object)[ 'A_STATUS' => 'has_ustalenia',   'cnt' => $statsSad->cnt_ustalenia, 'suma_zaleglosci' => null ];
		}
		// filter: 'sad_and_komornik', 'sad_bez_komornik'
		$statsKom = DB::getPDO()->fetchFirstAsObject("
			select w.`A_STATUS`
				, count(1) as cnt
				, sum(IF(w.`NR_SPRAWY_KOMORNIK`!='', 1, 0)) as cnt_komornik
			from  `USERS2_WINDYKACJA_STATUS` as w
				left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
			where
				w.`A_STATUS`='sad'
				{$sqlWhereAdd}
		");
		if ($statsKom) {
			$ret['sad_and_komornik'] = (object)[ 'A_STATUS' => 'sad_and_komornik', 'cnt' => $statsKom->cnt_komornik,                    'suma_zaleglosci' => null ];
			$ret['sad_bez_komornik'] = (object)[ 'A_STATUS' => 'sad_bez_komornik', 'cnt' => ($statsKom->cnt - $statsKom->cnt_komornik), 'suma_zaleglosci' => null ];
		}
		// filter: 'isMovedToVectra'
		$statsVectra = DB::getPDO()->fetchFirstAsObject("
			select w.`A_STATUS`
				, count(1) as cnt
				, sum(IF(w.`PAY_SALDO`<={$stanZeroLimit}, w.`PAY_SALDO`, 0)) as suma_zaleglosci
			from  `USERS2_WINDYKACJA_STATUS` as w
				left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
			where
				w.`IS_MOVED_TO_VECTRA`=1
				{$sqlWhereAdd}
		");
		if ($statsVectra) {
			$ret['isMovedToVectra'] = (object)[ 'cnt' => $statsVectra->cnt, 'suma_zaleglosci' => $statsVectra->suma_zaleglosci ];
		}
		return $ret;
	}

}