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

<?php

Lib::loadClass('Windykacja_BillingListDocs');
Lib::loadClass('Windykacja_StatsHelper');

class Windykacja_StatsModel {

	public static function _parse_query($q) {
		$sql_where = '';
		$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})";

		$filter_selected = Windykacja_StatsHelper::get_filter_selected(); // V::get('_f', '', $_GET);
		if ($filter_selected) {
			if ($filter_selected == 'po-terminie') {
				$sql_where_and_arr[] = "w.`PAY_SALDO`<=-0.05";
				$sql_where_and_arr[] = "w.`PAY_DATE`!='0000-00-00'";
				$sql_where_and_arr[] = "w.`PAY_DATE`<DATE_SUB(NOW(), INTERVAL 36 MONTH)";
			}
			else if ($filter_selected == '3 m-ce przed') {
				$sql_where_and_arr[] = "w.`PAY_SALDO`<=-0.05";
				$sql_where_and_arr[] = "w.`PAY_DATE`!='0000-00-00'";
				$sql_where_and_arr[] = "w.`PAY_DATE`<DATE_SUB(NOW(), INTERVAL 33 MONTH)";
				$sql_where_and_arr[] = "w.`PAY_DATE`>DATE_SUB(NOW(), INTERVAL 36 MONTH)";
			}
			else if ($filter_selected == 'tel1') {
				$sql_where_and_arr[] = "w.`PAY_SALDO`<=-0.05";
				$sql_where_and_arr[] = "w.`A_STATUS` in('wezwanie1', 'waiting-wezwanie2', 'wezwanie2', 'waiting-krd')";
				$sql_where_and_arr[] = "( w.`LAST_PHONE_STATUS_DATE`='0000-00-00'
					or w.`LAST_PHONE_STATUS_DATE`<DATE_SUB(NOW(), INTERVAL 3 MONTH) )";
			}
			else if ($filter_selected == 'bad_address') {
				$sql_where_and_arr[] = "w.`BAD_ADDRESS`>0";
				$sql_where_and_arr[] = "w.`PAY_SALDO`<=-0.05";
			}
			else if ($filter_selected == 'stan_zero') {
				$sql_where_and_arr[] = "w.`A_STATUS`='WAITING'";
				$sql_where_and_arr[] = "w.`PAY_SALDO`>-0.05";
			}
			else if ($filter_selected == '10-ego') {
				$sql_where_and_arr[] = "w.`A_STATUS`='WAITING'";
				$sql_where_and_arr[] = "w.`PAY_SALDO`<=-0.05";
			}
			else if ($filter_selected == 'has_nr_sad') {
				$sql_where_and_arr[] = "w.`NR_SPRAWY_SAD`!=''";
			}
			else if ($filter_selected == 'has_nr_komornik') {
				$sql_where_and_arr[] = "w.`NR_SPRAWY_KOMORNIK`!=''";
			}
			else if ($filter_selected == 'has_ustalenia') {
				$sql_where_and_arr[] = "w.`L_APPOITMENT_INFO`!=''";
			}
			else if ($filter_selected == 'sad_and_komornik') {
				$sql_where_and_arr[] = "w.`A_STATUS`='sad' and w.`NR_SPRAWY_KOMORNIK`!=''";
			}
			else if ($filter_selected == 'sad_bez_komornik') {
				$sql_where_and_arr[] = "w.`A_STATUS`='sad' and w.`NR_SPRAWY_KOMORNIK`=''";
			}
			else if ($filter_selected == 'isMovedToVectra') {
				$sql_where_and_arr[] = "w.`IS_MOVED_TO_VECTRA`=1";
			}
			else {
				$sql_where_and_arr[] = "w.`A_STATUS`='{$filter_selected}'";
			}
		}
		if (!empty($q)) {
			$q = trim($q);
			if (is_numeric($q)) {// billing number
				$sqlIdUsr = $q;
				$sql_where_and_arr[] = "a.`id_users`='{$sqlIdUsr}'";
			}
			else {// string - name, second name
				$q_arr_values = array();
				$q_exp = explode(' ', $q);
				foreach ($q_exp as $v_q) {
					$v_q = trim($v_q);
					if (strlen($v_q) > 2) {// min. 3 znaki
						$q_arr_values[] = strtolower($v_q);
					}
				}

				foreach ($q_arr_values as $q_value) {
					$sql_filter_q = array();
					$q_arr = array();// uniq array
					$q_arr_pl = array();// loop array

					$pl_letters = array('ą', 'ć', 'ę', 'ł', 'ń', 'ó', 'ś', 'ź', 'ż');
					$en_letters = array('a', 'c', 'e', 'l', 'n', 'o', 's', 'z', 'z');

					$sqlQValue = DB::getPDO()->quote("{$q_value}%");
					$sql_filter_q[] = " a.`P_NAME` like {$sqlQValue} ";
					$sql_filter_q[] = " a.`P_NAME_SECOND` like {$sqlQValue} ";

					$q_value_clean_en = str_replace($pl_letters, $en_letters, $q_value);
					$q_value_clean_pl = str_replace($en_letters, $pl_letters, $q_value_clean_en);
					$q_value_clean_pr = str_replace($en_letters, '_', $q_value_clean_en);
					if ($q_value_clean_en != $q_value) $q_arr[$q_value_clean_en] = true;
					if ($q_value_clean_pl != $q_value) $q_arr[$q_value_clean_pl] = true;
					if ($q_value_clean_pr != $q_value) $q_arr[$q_value_clean_pr] = true;

					foreach ($q_arr as $v_q => $v_val) {
						$sqlQValue = DB::getPDO()->quote($v_q);
						$sql_filter_q[] = " a.`P_NAME` like {$sqlQValue} ";
						$sql_filter_q[] = " a.`P_NAME_SECOND` like {$sqlQValue} ";
						// $sql_filter_q[] = "a.`P_ADDRESS_STREET` like '{$sqlQValue}'";
					}

					$sql_field_1 = " a.`P_NAME` ";
					$sql_field_2 = " a.`P_NAME_SECOND` ";
					foreach ($pl_letters as $k_ind => $v_char_pl) {
						$sql_field_1 = "REPLACE({$sql_field_1}, '{$v_char_pl}', '{$en_letters[$k_ind]}')";
						$sql_field_2 = "REPLACE({$sql_field_2}, '{$v_char_pl}', '{$en_letters[$k_ind]}')";
					}
					$q_value_clean_en = DB::getPDO()->quote("%{$q_value_clean_en}%");
					$sql_filter_q[] = $sql_field_1 . " like {$q_value_clean_en} ";
					$sql_filter_q[] = $sql_field_2 . " like {$q_value_clean_en} ";

					$sql_where_and_arr[] = "(" . implode(" or ", $sql_filter_q) . ")";
				}
			}
		}

		$special_filter_active = V::get('_special_filter_active', '', $_GET);
		if ('today' == $special_filter_active) {
			$today = date("Y-m-d");
			$sql_where_and_arr[] = "w.`A_STATUS_UPDATE_DATE`='{$today}'";
		}
		$special_filter_msg_mail = V::get('_special_filter_msg_mail', '', $_GET);
		if ('not_today' == $special_filter_msg_mail) {
			$sql_where_and_arr[] = "w.`LAST_PHONE_STATUS_DATE`<'{$today}'";
		}
		$special_filter_msg_sms = V::get('_special_filter_msg_sms', '', $_GET);
		if ('not_today' == $special_filter_msg_sms) {
			$sql_where_and_arr[] = "w.`LAST_PHONE_STATUS_DATE`<'{$today}'";
		}

		if (!empty($sql_where_and_arr)) {
			$sql_where = implode(" and ", $sql_where_and_arr);
		} else {
			$sql_where = "1=1";
		}
		return $sql_where;
	}

	public static function get_order_by_fields() {
		$order_by_fields = array();
		$order_by_fields[] = 'ID';
		$order_by_fields[] = 'is_firma';
		$order_by_fields[] = 'BILLING_OWNER';
		$order_by_fields[] = 'STATUS';
		$order_by_fields[] = 'BLOKADA';
		$order_by_fields[] = 'ID_BILLING_USERS';
		$order_by_fields[] = 'P_NAME';
		$order_by_fields[] = 'PAY_SALDO';
		$order_by_fields[] = 'PAY_FVAT';
		$order_by_fields[] = 'PAY_DATE';
		$order_by_fields[] = 'PAY_TERM';
		$order_by_fields[] = 'HAS_ACTIVE_NET';
		$order_by_fields[] = 'HAS_ACTIVE_TV';
		$order_by_fields[] = 'IS_MOVED_TO_VECTRA';
		return $order_by_fields;
	}

	public static function get_users_total($q = '') {
		$sql_where = self::_parse_query($q);
		return DB::getPDO()->fetchValue("
			select count(1) as cnt
			from `USERS2_WINDYKACJA_STATUS` as w
				left join `BILLING_USERS_ADD` as a on ( a.`id_users` = w.`ID_BILLING_USERS` )
			where {$sql_where}
		");
	}

	public static function get_sql_users_select() {
		$sql_select = "
				a.`id_users` as ID
				, a.`id_users`
				, a.`P_NAME`
				, a.`P_NAME_SECOND`
				, a.`P_ADDRESS_STREET`
				, a.`P_ADDRESS_HOME`
				, a.`P_ADDRESS_HOUSE`
				, a.`P_ADDRESS_CITY`
				, a.`P_ADDRESS_POST_CODE`
				, a.`P_ADDRESS_REGION`
				, a.`P_PESEL`
				, a.`P_PHONE`
				, a.`P_OTHER_DOC`
				, a.`user_mail_contact`
				, a.`is_firma`
				, a.`P_ADRESS_KORESP_1282`
				, w.`ID` as WINDYKACJA_ID
				, w.`ID_BILLING_USERS`
				, w.`A_STATUS`
				, w.`A_STATUS_UPDATE_DATE`
				, w.`USER_PAY_TERM_ADD`
				, w.`L_APPOITMENT_DATE`
				, w.`L_APPOITMENT_USER`
				, w.`L_APPOITMENT_INFO`
				, w.`PAY_DATE`
				, w.`PAY_DATE_FIRST_FVAT`
				, w.`PAY_TERM`
				, w.`PAY_SALDO`
				, w.`PAY_FVAT`
				, w.`wezwanie1_DATE`
				, w.`wezwanie2_DATE`
				, w.`wpis_w_krd_DATE`
				, w.`ID_KORESP`
				, w.`LAST_ID_KORESP_WEZWANIE1`
				, w.`LAST_ID_KORESP_WEZWANIE2`
				, w.`LAST_PAY_DATE`
				, w.`LAST_PAY_VALUE`
				, w.`LAST_FVAT_PAY_TERM`
				, w.`LAST_FVAT_VALUE`
				, w.`PAY_SALDO_30_DNI`
				, w.`PAY_SALDO_ISSUED`
				, w.`BAD_ADDRESS`
		--		, (select bu.`BILLING_OWNER` from `BILLING_USERS` as bu where bu.`ID`=w.`ID_BILLING_USERS` limit 1) as BILLING_OWNER
		--		, (select bu.`STATUS` from `BILLING_USERS` as bu where bu.`ID`=w.`ID_BILLING_USERS` limit 1) as STATUS
				, bu.`STATUS`
				, bu.`BILLING_OWNER`
				, w.`NR_SPRAWY_KRD`
				, w.`NR_SPRAWY_SAD`
				, w.`NR_SPRAWY_KOMORNIK`
				, w.`ACCOUNT_NUMBER`
				, w.`SERVICES_STREETS`
				, w.`IS_MOVED_TO_VECTRA` as IS_MOVED_TO_VECTRA
		--		, ('REZYGNACJA_Z_PRZEJSCIEM_DO_VECTRA' = (select `RODZAJ_DZIALANIA_HANDLOWEGO`
		--				from `DEALS_TABLE`
		--				where `A_STATUS` = 'NORMAL'
		--					and `ID_BILLING_USERS` = w.`ID_BILLING_USERS`
		--				order by `ID` DESC
		--				limit 1)
		--			) as IS_MOVED_TO_VECTRA
				, w.`HAS_ACTIVE_NET`
				, w.`HAS_ACTIVE_TV`
		";
		return $sql_select;
	}

	public static function get_users($q = '', $limit = 10, $limit_start = 0, $order_by = '', $order_dir = '') {
		$ret = array();

		$sql_select = self::get_sql_users_select();
		$sql_where = self::_parse_query($q);
		$sql_limit = "limit " . (($limit > 0)? $limit : "10");
		if ($limit_start > 0) $sql_limit .= " offset {$limit_start}";
		$sql_order_by = "";
		$order_by_allowed = self::get_order_by_fields();
		if (in_array($order_by, $order_by_allowed)) {
			if (in_array($order_by, array('P_NAME','is_firma'))) {
				$order_by = "a.{$order_by}";
			} else if (in_array($order_by, array('BILLING_OWNER','STATUS'))) {
				$order_by = "bu.{$order_by}";
			} else if ($order_by == 'BLOKADA') {
				$order_by = "bu.`STATUS`";
			} else {
				$order_by = "w.{$order_by}";
			}
			$sql_order_by .= " order by {$order_by}";
			if (in_array($order_dir, array('DESC','ASC'))) {
				$sql_order_by .= " {$order_dir}";
			}
		}
		return array_map(function ($item) {
			return (object)$item;
		}, DB::getPDO()->fetchAllByKey("
			select {$sql_select}
			from `USERS2_WINDYKACJA_STATUS` as w
				left join `BILLING_USERS` as bu on(bu.`ID`=w.`ID_BILLING_USERS`)
				left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
			where {$sql_where}
			{$sql_order_by}
			{$sql_limit}
		", $key = 'id_users'));
	}

	public static function get_user_by_id($id) {
		$ret = null;
		if ($id <= 0) return $ret;

		$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);

		$sql_select = self::get_sql_users_select();
		return DB::getPDO()->fetchFirstAsObject("
			select {$sql_select}
			from `USERS2_WINDYKACJA_STATUS` as w
				left join `BILLING_USERS` as bu on ( bu.`ID` = w.`ID_BILLING_USERS` )
				left join `BILLING_USERS_ADD` as a on ( a.`id_users` = w.`ID_BILLING_USERS` )
			where a.`id_users` = :id_user
				{$sqlWhereAdd}
		", [ ':id_user' => $id ]);
		// if(0){
		// 	$r->BA_WINIEN = "";
		// 	$r->BA_MA = "";
		// 	$r->BA_TIMESTAMP = "";
		// 	if (DB::getPDO('931')) {
		// 		$r_fin = DB::getPDO('931')->fetchFirst("
		// 			select BA.`WINIEN` as BA_WINIEN
		// 				, BA.`MA` as BA_MA
		// 				, unix_timestamp(BA.TIMESTAMP) as BA_TIMESTAMP
		// 			from `BILLING_ACCOUNTS` as BA
		// 			where BA.`ID_BILLING_USERS` = :id_user
		// 		", [ ':id_user' => $r->ID ]);
		// 		if ($r_fin) {
		// 			$r->BA_WINIEN = $r_fin['BA_WINIEN'];
		// 			$r->BA_MA = $r_fin['BA_MA'];
		// 			$r->BA_TIMESTAMP = $r_fin['BA_TIMESTAMP'];
		// 		}
		// 	}
		// }
	}

	public static function get_company(&$user) {
		if (!isset($user->_company)) {
			$user->_company = DB::getPDO()->fetchFirstAsObject("
				select
					bo.`id`
					, bo.`name1`
					, bo.`name2`
					, bo.`kod`
					, bo.`miasto`
					, bo.`ulica`
					, bo.`numer_dom`
					, bo.`tel`
					, bo.`fax`
					, bo.`nip`
					, bo.`regon`
					, bo.`bank`
					, bo.`nr_rach`
					, bo.`NR_RACH_MASS_PAY`
					, bo.`BILLING_OWNER_EMAIL`
				from `BILLING_USERS` as bu
					left join `BILLING_OWNER` as bo on ( bo.`ID` = bu.`BILLING_OWNER` )
				where
					bu.`ID` = :id_user
			", [ ':id_user' => $user->ID_BILLING_USERS ]);
			/*
				id					1 / 2
				name1				"BIALL-NET" Sp. z o.o. / "NET-DAY" s.c.
				name2				--- / Adrian i Ewa Wieczorkowscy
				kod     		80-174 / 80-809
				miasto			Gdańsk, Otomin / Gdańsk
				ulica				Słoneczna / Cieszyńskiego
				numer_dom		43 / 38
				numer_pos		NULL / NULL
				uwagi				--- / ---
				tel					0-58 320-72-92 / 0-58 741 84 54
				fax					0-58 320-72-96 / 0-58 741 84 56
				nip					593-22-68-672 / 583-27-54-031
				regon				192120212 / 192578721
				bank				Bank Zachodni WBK SA I O/Gdansk / ---
				nr_rach			46 1090 1098 0000 0001 0253 7156 / 84 1500 1171 1211 7002 9997 0000
				NR_RACH_MASS_PAY		109000049887 / 109000049669
				BILLING_OWNER_EMAIL	biall-net@biall.net.pl / netday@netday.pl
			 */
		}
		return $user->_company;
	}

	public static function get_billing_type($type_id) {
		$ret = null;
		$types = self::get_billing_types();
		if (array_key_exists($type_id, $types)) {
			$ret = $types[$type_id];
		}
		return $ret;
	}

	public static function get_billing_type_desc($type) {
		$ret = '';
		if ($type == 'FVAT') {
			return "Faktura Vat";
		}
		$types = self::get_billing_types();
		foreach ($types as $h) {
			if ($h['TYPE'] == $type) {
				return $h['DESC'];
			}
		}
		return $ret;
	}

	public static function get_billing_types($ids = array()) {
		static $_cache;
		if (empty($_cache)) $_cache = array();
		$cache_key = implode(".", $ids);
		if (!array_key_exists($cache_key, $_cache)) {
			$billing_types = array();
			if (DB::getPDO('931')) {
				$sql_where = " 1=1 ";
				if (!empty($ids)) {
					$sql_where = " `ID` in ('".implode("','", $ids)."') ";
				}
				$billing_types = DB::getPDO('931')->fetchAllByKey("
					select *
					from `BILLING_NUMBERS_TYPE`
					where
						{$sql_where}
				", $key = 'ID');
			}
			$_cache[$cache_key] = $billing_types;
		}
		return $_cache[$cache_key];
	}

	public static function getAllServicesStreetsCSV($user) {
		$streets = DB::getPDO()->fetchAllByKey("
			select s.`S_ADDRESS_STREET`
			from `COMPANIES` c
				join `DEALS_TABLE` d on(d.`ID_BILLING_USERS`=c.`ID`)
				join `SERVICES` s on(s.`ID_BILLING_USERS`=c.`ID` and s.`ID_DEALS_TABLE`=d.`ID`)
			where c.`ID` = :id_user
		--		and c.`A_ADM_COMPANY`='19994_PODMIOT_Vectra_wlasciciel'
				and s.`NAME_LIST_SERVICES` in ('TV','USERS2','VOIP')
				and s.`A_STATUS` not in ('DELETED')
		", $key = 'S_ADDRESS_STREET', [ ':id_user' => $user->ID ]);
		return (!empty($streets))? implode(',', array_keys($streets)) : '';
	}

	public static function get_umowy_from_l2($user) {
		return DB::getPDO()->fetchAllByKey("
			select
				d.`ID`, d.`P_DEALPREFIX`, d.`P_DEALNUMBER`, d.`P_DEALNUMBER_OLD`, d.`P_DEALDATE`, d.`P_DEALDATE_TERM`, d.`DEALDESC`, d.`ANEX_NEEDED`, d.`ANEX_CONFIRM_DATE`
			from `DEALS_TABLE` as d
			where d.`ID_BILLING_USERS` = :id_user
			order by d.`ID` DESC
		", $key = 'ID', [ ':id_user' => $user->ID ]);
	}

	public static function get_uslugi_from_l2(&$user) {
		// l2: www/modules/webone/form/edit_user_status.php
		// Aktualne: Usługa[Status] => list_services2($user->ID);
		if(0){//	SERVICES table struct example:
			//[ID_BILLING_USERS] => 11363
			//[active] => 1
			//[TAB_UPDATE_STAT] => 1
			//[TAB_UPDATE_DATE] => 2012-02-03 17:43:44
			//[TAB_UPDATE_INFO] => OK-PREUPDATE-DELETED---UPDATED-OK-:195.117.2.79
			//[SERVICE_TYPE] => ABONAMENT
			//[ID_BILLING_NUMBERS_TYPE] => 1
			//[ABONAMENT_PERIOD] => 1
			//[ABONAMENT_VALUE] => 26.01
			//[VAT] => 23
			//[ID_CURRENCY] => 0
			//[ABONAMENT_PAYMENT] => UP
			//[ABONAMENT_START] => 1
			//[MINIMUM_BILLING_VALUE] => 0.00
			//[BILLED_TILL] => 2012-06-01 00:00:00
			//[HANGUP_FROM] => 
			//[HANGUP_TILL] => 
			//[HANGUP_STATUS] => 
			//[HANGUP_RATIO] => 1.00
			//[TIMESTAMP] => 20120504125053
			//[P_ID_SERVICES] => 0
			//[uwagi] => 
			//[ID_DEALS_TABLE] => 9373
			//[VAT_NAME] => 23
			//[id_list_sww] => 1
			//[SERV_ID_BILLING_PREFIXES] => 0
			//[ID_OFFERS] => 833
			//[A_RECORD_CREATE_DATE] => 
			//[A_RECORD_CREATE_AUTHOR] => 
			//[A_RECORD_UPDATE_DATE] => 
			//[A_RECORD_UPDATE_AUTHOR] => 
		}
		return DB::getPDO()->fetchAllByKey("
			select
				s.`ID`, s.`NAME_LIST_SERVICES`, s.`A_STATUS`
				, s.`ID_OFFERS`
				, s.`ID_DEALS_TABLE`
				, s.`P_ID_SERVICES`
				, s.`P_ID_SERVICES` as P_ID
				, s.`HANGUP_STATUS`
				, s.`HANGUP_FROM`
				, s.`HANGUP_TILL`
				, ls.`description` as description
				, lst.`name` as A_STATUS_DESC
			from `SERVICES` as s
				left join `LIST_SERVICES` as ls on(ls.`name`=s.`NAME_LIST_SERVICES`)
				left join `LIST_status` as lst on(lst.`ses_users2`=s.`HANGUP_STATUS` and lst.`ID`>2)
			where `ID_BILLING_USERS` = :id_user
		", $key = 'ID', [ ':id_user' => $user->ID ]);
	}

	public static function get_umowy_from_l3(&$user) {
		return DB::getPDO()->fetchAll("
			select d.*
			from `USERS2_DEALS` as d
			where
				( d.`ID_BILLING_USERS_OLD` = :id_user or d.`ID_BILLING_USERS_NEW` = :id_user )
			order by d.`ID` DESC
		", [ ':id_user' => $user->ID ]);
	}

	public static function get_bill_dosc_by_date(&$user, $date_limit = null) {
		// fetch finanse data from remote DB
		$billing_docs = new Windykacja_BillingListDocs();
		$pdo_webone = DB::getPDO('931');
		if (!$pdo_webone) {
			echo'<p style="color:red">'."Blad polaczenia do bazy dancyh webone_billing - Zasob [931]".'</p>';
			return;
		}
		$sqlWhereAdd = " ";
		// blad w zaokragleniach
		//		, sum((BILLS_FVAT_POS.AMMOUNT * BILLS_FVAT_POS.PRICE)*(1 + BILLS_FVAT_POS.VAT/100)) as WARTOSC
		if ($date_limit) {
			$sqlWhereAdd .= " and BILLS_FVAT.PAYMENT_TERM <= '{$date_limit}' ";
		}
		$billsFvat = $pdo_webone->fetchAll("
			select
				BILLS_FVAT.*
				, count(BILLS_FVAT_POS.ID) as ILOSC_POZYCJI
				, BILLS_FVAT_POS.ID_BILLS_FVAT
				, sum(round((round(BILLS_FVAT_POS.AMMOUNT * BILLS_FVAT_POS.PRICE, 2)) * (1 + BILLS_FVAT_POS.VAT / 100), 2)) as WARTOSC
				-- , count(BILLS_FVAT_POS.PRICE) as ILOSC_POZYCJI
				, `BILLING_NUMBERS`.`NUMBER` as `NUMBER`
				, `BILLING_NUMBERS`.`ID_BILLING_PREFIXES` as `ID_BILLING_PREFIXES`
			from `BILLS_FVAT`
				left join `BILLING_NUMBERS` on(`BILLING_NUMBERS`.`ID`=`BILLS_FVAT`.`ID_BILLING_NUMBERS`)
				left join `BILLS_FVAT_POS` on(`BILLS_FVAT_POS`.`ID_BILLS_FVAT`=`BILLS_FVAT`.`ID`)
			where BILLS_FVAT.OPEN='N' and BILLS_FVAT.ID_BILLING_USERS = :id_user
				{$sqlWhereAdd}
			group by BILLS_FVAT.ID
			order by BILLS_FVAT.ID_BILLING_NUMBERS ASC
		", [ ':id_user' => $user->ID ]);
		foreach ($billsFvat as $h) {
			// if (0){// ? dla kazdej faktury ?
			// $sql = "select t1.ID_BILLING_NUMBERS AS NR_DOK
			// 		, t1.ID_BILLING_USERS as PLATNIK
			// 		, t1.WINIEN,t1.MA
			// 		, t1.ID_FIN_WINIEN as ID_F_W
			// 		, t1.ID_FIN_WINIEN_VAL as F_WINIEN_V
			// 		, t1.ID_FIN_MA as ID_F_M
			// 		, t1.ID_FIN_MA_VAL as F_MA_V
			// 		, max(t2.ID)
			// 		, sum(t2.ID_FIN_WINIEN_VAL) as ROZ_WINIEN
			// 		, max(t3.ID)
			// 		, sum(t3.ID_FIN_MA_VAL) as ROZ_MA
			// 		, t1.WINIEN - sum(t2.ID_FIN_WINIEN_VAL) - sum(t3.ID_FIN_MA_VAL) as POZ_WINIEN
			// 		, t1.MA - sum(t3.ID_FIN_MA_VAL) - sum(t2.ID_FIN_WINIEN_VAL) as POZ_MA
			// 	from `BILLING_ACCOUNTS_FILES` as t1
			// 		left join `BILLING_ACCOUNTS_FILES` as t2 on (t1.ID_BILLING_NUMBERS=t2.ID_FIN_WINIEN)
			// 		left join `BILLING_ACCOUNTS_FILES` as t3 on (t1.ID_BILLING_NUMBERS=t3.ID_FIN_MA)
			// 	where
			// 		t1.ID_FIN_WINIEN is NULL and t1.ID_FIN_MA is NULL and t1.MA = 0 and  t1.WINIEN > 0
			// 		and t1.ID_BILLING_NUMBERS={$user->ID}
			// 	group by t1.ID_BILLING_NUMBERS
			// 	limit 0,1
			// ";
			// }
			//$h['type'] = 'FVAT';
			//$h['nr'] = $h['ID_BILLING_NUMBERS'];
			//$h['WINIEN'] = $h['WARTOSC'];
			//$h['MA'] = 0;
			$billing_docs->add_bill_doc($h['PAYMENT_TERM'], 'FVAT', $h);
		}

		$faktury_cols = array();
		//$faktury_cols['ID_BILLING_USERS'] = "id klienta";// 
		$faktury_cols['ID_BILLING_NUMBERS'] = "numer";// numer faktury
		$faktury_cols['BILL_DATE'] = "";// data('Y-m-d')
		$faktury_cols['SELL_DATE'] = "";// data('Y-m-d')
		$faktury_cols['PAYMENT_TERM'] = "";// data('Y-m-d')

		//$billing_types = self::get_billing_types(array(3, 4, 5, 7, 8));
		$billing_types = self::get_billing_types();
		//echo'<pre>$billing_types:';print_r($billing_types);echo'</pre>';

		foreach ($billing_types as $bill_type) {
			if ($bill_type['CLASS'] == 'FINANCE') {
				$sql_where = ($date_limit)? " and {$bill_type['TABLE']}.BILL_DATE <= '{$date_limit}' " : "";
				$sql = "
					select {$bill_type['TABLE']}.ID
						, {$bill_type['TABLE']}.ID_BILLING_NUMBERS
						, {$bill_type['TABLE']}.BILL_DATE
						, round({$bill_type['TABLE']}.WINIEN, 2) as WINIEN
						, round({$bill_type['TABLE']}.MA, 2) as MA
						, {$bill_type['TABLE']}.ID_FK_DEKRET
						, {$bill_type['TABLE']}.OPEN
						, {$bill_type['TABLE']}.FK_ZAKSIEG
						, {$bill_type['TABLE']}.do_dokumentu
						, {$bill_type['BILLING_NUMBERS_TABLE']}.NUMBER
						, {$bill_type['BILLING_NUMBERS_TABLE']}.ID_BILLING_PREFIXES
						, {$bill_type['BILLING_NUMBERS_TABLE']}.ID_BILLING_NUMBERS_TYPE
					from {$bill_type['TABLE']}
						left join {$bill_type['BILLING_NUMBERS_TABLE']} on ({$bill_type['BILLING_NUMBERS_TABLE']}.ID={$bill_type['TABLE']}.ID_BILLING_NUMBERS)
					where {$bill_type['TABLE']}.ID_BILLING_USERS='{$user->ID}'
						{$sql_where}
				";
				$vBillDocs = $pdo_webone->fetchAll($sql);
				foreach ($vBillDocs as $h) {
					$billing_docs->add_bill_doc($h['BILL_DATE'], $bill_type['TYPE'], $h);
				}
			}
			else if ($bill_type['CLASS'] == 'SELL_MA') {
				$sql_where = ($date_limit)? " and t.BILL_DATE <= '{$date_limit}' " : "";
				$sql = "select t.ID
						, count(t_pos.ID) as cnt
						, t_pos.ID_BILLS_FVAT
						, t.ID_CURRENCY
						, round(sum((t_pos.N_AMMOUNT * t_pos.N_PRICE) * ( 1 + t_pos.N_VAT / 100)),2) as WINIEN
						, round(sum( 
								((t_pos.AMMOUNT * t_pos.PRICE) * ( 1 + t_pos.VAT / 100))
								- (t_pos.N_AMMOUNT * t_pos.N_PRICE * t_pos.N_VAT / 100)
								+ (t_pos.N_AMMOUNT * t_pos.N_PRICE * t_pos.VAT / 100)
							),2) as MA
						, t.ID_BILLING_NUMBERS
						, t.OPEN
						, t.FK_ZAKSIEG
						, t.IF_KORV , t.BILL_DATE
						, t_num.`NUMBER`
						, t_num.`ID_BILLING_PREFIXES`
						, fv_num.`NUMBER` as FV_NUMBER
					from `{$bill_type['TABLE']}` t
						left join `{$bill_type['BILLING_NUMBERS_TABLE']}` t_num on (t_num.`ID`=t.`ID_BILLING_NUMBERS`)
						left join `{$bill_type['BILLING_NUMBERS_TABLE']}` fv_num on (fv_num.`ID`=t.`REMOTE_ID_BILLING_NUMBERS`)
						left join `{$bill_type['TABLE']}_POS` t_pos on (t_pos.`ID_BILLS_FVAT`=t.`ID`)
					where t.`ID_BILLING_USERS`='{$user->ID}'
						{$sql_where}
					group by t.`ID`
					order by t.`ID`
				";
				$vBillDocs = $pdo_webone->fetchAll($sql);
				foreach ($vBillDocs as $h) {
					$billing_docs->add_bill_doc($h['BILL_DATE'], $bill_type['TYPE'], $h);
				}
			}
		}

		return $billing_docs;
	}

	/**
	 * @used in Windykacja_FunkcjeL1::billing_billwiev_przeterminowania
	 */
	public static function get_billing_account_files($user_id) {
		$baf = array();
		$ECHO_PARAM = '';
		$ID_BILLING_USERS_PARAM = $user_id;

		if (!DB::getPDO('931')) {
			echo'<p style="color:red">'."Blad polaczenia do bazy dancyh webone_billing - Zasob [931]".'</p>';
			return $baf;
		}

		$WHERE = "";
		if ($ID_BILLING_USERS_PARAM) $WHERE .= " and BAF.ID_BILLING_USERS='{$ID_BILLING_USERS_PARAM}' ";
		//UZGODNIENIE_SALD
		if (strstr($ECHO_PARAM, 'saldo_rok')) {
			global $base_path ;
			include_once($base_path."/modules/billing/bill/modules/billing_financefunc.inc");
			if ($ID_BILLING_USERS_PARAM) {
				if ($vdb->sql_numrows($vdb->sql_query("select ID from BILLING_ACCOUNTS_TILL where ID_BILLING_USERS='{$ID_BILLING_USERS_PARAM}' and TILL=YEAR(NOW())-1 ;")) != 1) {
					UAKTUALNIJ_KONTA($ID_BILLING_USERS_PARAM);
				}
			}
			$WHERE .= " AND YEAR(BN.TIMESTAMP)<YEAR(NOW()) ";
		}
		$WHERE_FIRMA = "";
		if ($_GET['FIRMA']) $WHERE_FIRMA = "and BILLING_USERS.BILLING_OWNER='{$_GET['FIRMA']}'" ;
		//$billing_account_files = self::
		$sql = "
			create temporary table TEMP_BILLING_ACCOUNTS_FILES
			select BAF.ID
				, BAF.ID_BILLING_USERS
				, BAF.WINIEN
				, BAF.MA
				, BAF.ID_BILLING_NUMBERS_TYPE
				, BN.NUMBER
				, BN.ID_BILLING_PREFIXES
				, BAF_WINIEN.ID_FIN_WINIEN
				, BAF_WINIEN.ID_BILLING_NUMBERS_TYPE as ID_BILLING_NUMBERS_TYPE_W
				, BAF_BN_WINIEN.NUMBER as NUMBER_W
				, BAF_BN_WINIEN.ID_BILLING_PREFIXES as ID_BILLING_PREFIXES_W
				, unix_timestamp(BAF_BN_WINIEN.TIMESTAMP) as TIMESTAMP_W
				, BAF_WINIEN.ID_FIN_WINIEN_VAL
				, NULL as ID_FIN_MA
				, NULL as	ID_BILLING_NUMBERS_TYPE_M
				, NULL as	NUMBER_M
				, NULL as ID_BILLING_PREFIXES_M
				, NULL as TIMESTAMP_M
				, NULL as	ID_FIN_MA_VAL
				, unix_timestamp(ifnull(BILLS_FVAT.PAYMENT_TERM, BILLS_KORV.PAYMENT_TERM)) as TERMIN_PL
				, BUA.P_NAME
				, BUA.P_NAME_SECOND
				, BUA.P_ADDRESS_CITY
				, BUA.P_ADDRESS_STREET
				, BUA.P_ADDRESS_HOUSE
				, BUA.P_ADDRESS_HOME
				, BUA.user_mail_contact
				, BA.WINIEN as BA_WINIEN
				, BA.MA as BA_MA
				, unix_timestamp(BA.TIMESTAMP) as BA_TIMESTAMP
				, unix_timestamp(BAF.TIMESTAMP) as BAF_TIMESTAMP
			from BILLING_ACCOUNTS_FILES as BAF
				left join BILLING_USERS on BILLING_USERS.ID=BAF.ID_BILLING_USERS 
				left join BILLING_USERS_ADD  as BUA on BUA.id_users=BAF.ID_BILLING_USERS
				left join BILLING_NUMBERS as BN on BN.ID=BAF.ID_BILLING_NUMBERS 
				left join BILLING_ACCOUNTS as BA on BA.ID_BILLING_USERS=BAF.ID_BILLING_USERS
				left join BILLS_FVAT on BILLS_FVAT.ID_BILLING_NUMBERS=BAF.ID_BILLING_NUMBERS 
				left join BILLS_KORV on BILLS_KORV.ID_BILLING_NUMBERS=BAF.ID_BILLING_NUMBERS 
				left join BILLING_ACCOUNTS_FILES as BAF_WINIEN on BAF_WINIEN.ID_FIN_WINIEN=BAF.ID_BILLING_NUMBERS 
				left join BILLING_NUMBERS as BAF_BN_WINIEN on BAF_BN_WINIEN.ID=BAF_WINIEN.ID_BILLING_NUMBERS
			where BAF.ID_FIN_WINIEN is NULL and BAF.ID_FIN_MA is NULL and BAF.ID_FIN_WINIEN is NULL
				{$WHERE}
				{$WHERE_FIRMA}
		";

		$sql2 = "
			insert into TEMP_BILLING_ACCOUNTS_FILES
			select BAF.ID
				, BAF.ID_BILLING_USERS
				, BAF.WINIEN
				, BAF.MA
				, BAF.ID_BILLING_NUMBERS_TYPE
				, BN.NUMBER
				, BN.ID_BILLING_PREFIXES
				, NULL as ID_FIN_WINIEN
				, NULL as ID_BILLING_NUMBERS_TYPE_W
				, NULL as NUMBER_W
				, NULL as ID_BILLING_PREFIXES_W
				, NULL as TIMESTAMP_W
				, NULL as ID_FIN_WINIEN_VAL
				, BAF_MA.ID_FIN_MA
				, BAF_MA.ID_BILLING_NUMBERS_TYPE as ID_BILLING_NUMBERS_TYPE_M
				, BAF_BN_MA.NUMBER as NUMBER_M
				, BAF_BN_MA.ID_BILLING_PREFIXES as ID_BILLING_PREFIXES_M
				, unix_timestamp(BAF_BN_MA.TIMESTAMP) as TIMESTAMP_M
				, BAF_MA.ID_FIN_MA_VAL
				, unix_timestamp(ifnull(BILLS_FVAT.PAYMENT_TERM, BILLS_KORV.PAYMENT_TERM))
				, BUA.P_NAME
				, BUA.P_NAME_SECOND
				, BUA.P_ADDRESS_CITY
				, BUA.P_ADDRESS_STREET
				, BUA.P_ADDRESS_HOUSE
				, BUA.P_ADDRESS_HOME
				, BUA.user_mail_contact
				, BA.WINIEN as BA_WINIEN
				, BA.MA as BA_MA
				, unix_timestamp(BA.TIMESTAMP) as BA_TIMESTAMP
				, unix_timestamp(BAF.TIMESTAMP) as BAF_TIMESTAMP
			from BILLING_ACCOUNTS_FILES as BAF
				left join BILLING_USERS on (BILLING_USERS.ID=BAF.ID_BILLING_USERS)
				left join BILLING_USERS_ADD as BUA on (BUA.id_users=BAF.ID_BILLING_USERS)
				left join BILLING_NUMBERS as BN on (BN.ID=BAF.ID_BILLING_NUMBERS)
				left join BILLING_ACCOUNTS as BA on (BA.ID_BILLING_USERS=BAF.ID_BILLING_USERS)
				left join BILLS_FVAT on (BILLS_FVAT.ID_BILLING_NUMBERS=BAF.ID_BILLING_NUMBERS)
				left join BILLS_KORV on (BILLS_KORV.ID_BILLING_NUMBERS=BAF.ID_BILLING_NUMBERS)
				left join BILLING_ACCOUNTS_FILES as BAF_MA on (BAF_MA.ID_FIN_MA=BAF.ID_BILLING_NUMBERS)
				left join BILLING_NUMBERS as BAF_BN_MA on (BAF_BN_MA.ID=BAF_MA.ID_BILLING_NUMBERS)
			where
				BAF.ID_FIN_WINIEN is NULL and BAF.ID_FIN_MA is NULL
				{$WHERE}
				{$WHERE_FIRMA}
		";
		if (strstr($ECHO_PARAM,'saldo_rok')) {
			$sql = "
				create temporary table TEMP_BILLING_ACCOUNTS_FILES
				select
					BAF.ID, BAF.ID_BILLING_USERS , BAF.WINIEN, BAF.MA, BAF.ID_BILLING_NUMBERS_TYPE, BN.NUMBER , BN.ID_BILLING_PREFIXES,
					BAF_WINIEN.ID_FIN_WINIEN,
					BAF_WINIEN.ID_BILLING_NUMBERS_TYPE as  ID_BILLING_NUMBERS_TYPE_W,
					BAF_BN_WINIEN.NUMBER as  NUMBER_W,
					BAF_BN_WINIEN.ID_BILLING_PREFIXES as  ID_BILLING_PREFIXES_W,
					unix_timestamp(BAF_BN_WINIEN.TIMESTAMP) as  TIMESTAMP_W,
					BAF_WINIEN.ID_FIN_WINIEN_VAL,
					NULL as  ID_FIN_MA, 
					NULL as	 ID_BILLING_NUMBERS_TYPE_M,
					NULL as	 NUMBER_M,
					NULL as  ID_BILLING_PREFIXES_M,
					NULL as  TIMESTAMP_M,
					NULL as	 ID_FIN_MA_VAL,
					unix_timestamp(ifnull(BILLS_FVAT.PAYMENT_TERM,ifnull( BILLS_KORV.PAYMENT_TERM,BN.TIMESTAMP ))) as TERMIN_PL ,
					BUA.P_NAME , BUA.P_NAME_SECOND  , BUA.P_ADDRESS_CITY , BUA.P_ADDRESS_STREET ,BUA.P_ADDRESS_HOUSE  , BUA.P_ADDRESS_HOME , BUA.user_mail_contact ,
					BA.WINIEN as BA_WINIEN , BA.MA as BA_MA , unix_timestamp(BA.TIMESTAMP) as BA_TIMESTAMP,
					unix_timestamp(BAF.TIMESTAMP) as BAF_TIMESTAMP, 
					BILLING_ACCOUNTS_TILL.TILL as BAT_TILL , BILLING_ACCOUNTS_TILL.WINIEN as BAT_WINIEN, BILLING_ACCOUNTS_TILL.MA as BAT_MA 
				from BILLING_ACCOUNTS_FILES as BAF
					left join BILLING_USERS_ADD  as BUA on BUA.id_users=BAF.ID_BILLING_USERS
					left join BILLING_NUMBERS as BN on BN.ID=BAF.ID_BILLING_NUMBERS 
					left join BILLING_ACCOUNTS as BA on BA.ID_BILLING_USERS=BAF.ID_BILLING_USERS
					left join BILLS_FVAT on BILLS_FVAT.ID_BILLING_NUMBERS=BAF.ID_BILLING_NUMBERS 
					left join BILLS_KORV on BILLS_KORV.ID_BILLING_NUMBERS=BAF.ID_BILLING_NUMBERS 
					left join BILLING_ACCOUNTS_FILES as BAF_TRG on ( BAF.ID_FIN_WINIEN=BAF_TRG.ID_BILLING_NUMBERS or BAF.ID_FIN_MA=BAF_TRG.ID_BILLING_NUMBERS )
					left join BILLING_NUMBERS as BAF_TRG_BN on ( BAF_TRG.ID_BILLING_NUMBERS=BAF_TRG_BN.ID )
					left join BILLING_ACCOUNTS_FILES as BAF_WINIEN on ( BAF_WINIEN.ID_FIN_WINIEN=BAF.ID_BILLING_NUMBERS or BAF_WINIEN.ID_FIN_MA=BAF.ID_BILLING_NUMBERS )
					left join BILLING_NUMBERS as BAF_BN_WINIEN on ( BAF_BN_WINIEN.ID=BAF_WINIEN.ID_BILLING_NUMBERS )
					left join BILLING_ACCOUNTS_TILL on (BILLING_ACCOUNTS_TILL.ID_BILLING_USERS=BAF.ID_BILLING_USERS and BILLING_ACCOUNTS_TILL.TILL=year(now())-1  ) 
				where 
					( 	BAF.ID_FIN_WINIEN is NULL and BAF.ID_FIN_MA is NULL or year(BAF_TRG_BN.TIMESTAMP)>year(NOW())-1   )
					and (YEAR(BAF_BN_WINIEN.TIMESTAMP)<YEAR(NOW()) or BAF_BN_WINIEN.TIMESTAMP is NULL ) 	 
					{$WHERE}
				limit 2000 ;
			";

			$sql2 = "
				insert into TEMP_BILLING_ACCOUNTS_FILES select
					BAF.ID, BAF.ID_BILLING_USERS , BAF.WINIEN, BAF.MA, BAF.ID_BILLING_NUMBERS_TYPE, BN.NUMBER , BN.ID_BILLING_PREFIXES,
					NULL as ID_FIN_WINIEN,
					NULL as ID_BILLING_NUMBERS_TYPE_W,
					NULL as NUMBER_W ,
					NULL as ID_BILLING_PREFIXES_W,
					NULL as TIMESTAMP_W ,
					NULL as ID_FIN_WINIEN_VAL ,
					BAF_MA.ID_FIN_MA,
					BAF_MA.ID_BILLING_NUMBERS_TYPE as ID_BILLING_NUMBERS_TYPE_M,
					BAF_BN_MA.NUMBER as NUMBER_M,
					BAF_BN_MA.ID_BILLING_PREFIXES as ID_BILLING_PREFIXES_M,
					unix_timestamp(BAF_BN_MA.TIMESTAMP) as TIMESTAMP_M,
					BAF_MA.ID_FIN_MA_VAL,
					unix_timestamp(ifnull(BILLS_FVAT.PAYMENT_TERM,ifnull( BILLS_KORV.PAYMENT_TERM,BN.TIMESTAMP ))) as TERMIN_PL ,

					BUA.P_NAME , BUA.P_NAME_SECOND  , BUA.P_ADDRESS_CITY , BUA.P_ADDRESS_STREET ,BUA.P_ADDRESS_HOUSE  , BUA.P_ADDRESS_HOME , BUA.user_mail_contact ,
					BA.WINIEN as BA_WINIEN , BA.MA as BA_MA , unix_timestamp(BA.TIMESTAMP) as BA_TIMESTAMP,
					unix_timestamp(BAF.TIMESTAMP) as BAF_TIMESTAMP,
					BILLING_ACCOUNTS_TILL.TILL as BAT_TILL , BILLING_ACCOUNTS_TILL.WINIEN as BAT_WINIEN, BILLING_ACCOUNTS_TILL.MA as BAT_MA 
				from BILLING_ACCOUNTS_FILES as BAF
					left join BILLING_USERS_ADD  as BUA on BUA.id_users=BAF.ID_BILLING_USERS
					left join BILLING_NUMBERS as BN on BN.ID=BAF.ID_BILLING_NUMBERS
					left join BILLING_ACCOUNTS as BA on BA.ID_BILLING_USERS=BAF.ID_BILLING_USERS
					left join BILLS_FVAT on BILLS_FVAT.ID_BILLING_NUMBERS=BAF.ID_BILLING_NUMBERS
					left join BILLS_KORV on BILLS_KORV.ID_BILLING_NUMBERS=BAF.ID_BILLING_NUMBERS 
					left join BILLING_ACCOUNTS_FILES as BAF_TRG on ( BAF.ID_FIN_WINIEN=BAF_TRG.ID_BILLING_NUMBERS or BAF.ID_FIN_MA=BAF_TRG.ID_BILLING_NUMBERS )
					left join BILLING_NUMBERS as BAF_TRG_BN on ( BAF_TRG.ID_BILLING_NUMBERS=BAF_TRG_BN.ID )
					left join BILLING_ACCOUNTS_FILES as BAF_MA on ( BAF_MA.ID_FIN_WINIEN=BAF.ID_BILLING_NUMBERS or BAF_MA.ID_FIN_MA=BAF.ID_BILLING_NUMBERS )
					left join BILLING_NUMBERS as BAF_BN_MA on ( BAF_BN_MA.ID=BAF_MA.ID_BILLING_NUMBERS )
					left join BILLING_ACCOUNTS_TILL on (BILLING_ACCOUNTS_TILL.ID_BILLING_USERS=BAF.ID_BILLING_USERS and BILLING_ACCOUNTS_TILL.TILL=year(now())-1  ) 
				where
					( 	BAF.ID_FIN_WINIEN is NULL and BAF.ID_FIN_MA is NULL or year(BAF_TRG_BN.TIMESTAMP)>year(NOW())-1   )
					and BAF.ID_BILLING_USERS='1047'  AND YEAR(BN.TIMESTAMP)<YEAR(NOW())  
					and  (YEAR(BAF_BN_MA.TIMESTAMP)<YEAR(NOW())  or BAF_BN_MA.TIMESTAMP is NULL ) 	 
					{$WHERE}
				limit 1000,1000 ;
			";
		}//EOF if saldo_rok param

		$sql_alter = "
			alter table TEMP_BILLING_ACCOUNTS_FILES
			modify ID_FIN_MA int(11)
			, modify ID_BILLING_NUMBERS_TYPE_M int(2)
			, modify NUMBER_M int(10)
			, modify ID_BILLING_PREFIXES_M int(2) default NULL NULL
			, modify TIMESTAMP_M int(10)
			, modify ID_FIN_MA_VAL decimal(10,2)
			, modify ID_BILLING_PREFIXES int(2) default NULL NULL
			, modify ID_BILLING_PREFIXES_W int(2)  default NULL NULL
		";

		DB::getPDO('931')->execSql(" drop TEMPORARY table TEMP_BILLING_ACCOUNTS_FILES ");
		DB::getPDO('931')->execSql($sql);
		DB::getPDO('931')->execSql($sql_alter);
		DB::getPDO('931')->execSql($sql2);

		$count = DB::getPDO('931')->fetchValue(" select count(1) as cnt from TEMP_BILLING_ACCOUNTS_FILES ");
		if ($count <= 0 || $ID_BILLING_USERS_PARAM <= 0) {
			DB::getPDO('931')->execSql("
				insert into TEMP_BILLING_ACCOUNTS_FILES (ID, ID_BILLING_USERS, BAT_TILL, BAT_WINIEN, BAT_MA)
				select '0', ID_BILLING_USERS, TILL, WINIEN, MA
				from BILLING_ACCOUNTS_TILL
				where ID_BILLING_USERS = :id_user
					and TILL = YEAR(NOW()) - 1
			", [ ':id_user' => $ID_BILLING_USERS_PARAM ]);
		}
		$listBaf = DB::getPDO('931')->fetchAll(" select * from TEMP_BILLING_ACCOUNTS_FILES order by ID_BILLING_USERS, ID ");
		foreach ($listBaf as $h_baf) {
			$baf[] = array_values($h_baf); // like mysql_fetch_row which returm array [ 0 => val_1, 1 => val_2, ... ]
		}
		return $baf;
	}

	public static function update_doc_number(&$fvat_arr) {
		$fetch_ids = array();
		foreach ($fvat_arr as $k => $fvat) {
			$fetch_ids[] = $fvat['ID_BILLING_NUMBERS'];
		}
		if (!empty($fetch_ids)) {
			if (!DB::getPDO('931')) {
				return -1;
			}

			$bnum = DB::getPDO('931')->fetchAllByKey("
				select `ID`, `NUMBER`, `ID_BILLING_PREFIXES`
				from `BILLING_NUMBERS`
				where
					`ID` in(".implode(",", $fetch_ids).")
			", $key = 'ID');
			foreach ($bnum as $k_id => $bn) {
				foreach ($fvat_arr as $k_fvat => $fvat) {
					if ($bn['ID'] == $fvat['ID_BILLING_NUMBERS']) {
						$fvat_arr[$k_fvat]['NUMBER'] = $bn['NUMBER'];
						$fvat_arr[$k_fvat]['ID_BILLING_PREFIXES'] = $bn['ID_BILLING_PREFIXES'];
					}
				}				
			}
		}
	}

	/**
	 * @param $user - Windykacja status user
	 * @param $taks - wezwanie1, wezwanie2
	 * @param $id_proj - ID Projektu
	 * @returns int - ID created record
	 */
	public static function create_koresp($user, $task, $id_proj, $params = array()) {
		$sql_data = array();
		$sql_zawartosc = '';
		if ($task == 'wezwanie1') {
			$sql_zawartosc = 'wezwanie do zaplaty';
			$sql_data['K_TYP_KORESP'] = 'OUT';
			$sql_data['K_TYP_RODZAJ'] = 'POLECONY';
			$sql_data['K_LOKALIZACJA'] = 'PROJEKT';
			$sql_data['K_LOKALIZACJA_NEW'] = 'PROJEKT';
		} else if ($task == 'wezwanie2') {
			$sql_zawartosc = 'wezwanie do zaplaty - ostateczne';
			$sql_data['K_TYP_KORESP'] = 'OUT';
			$sql_data['K_TYP_RODZAJ'] = 'POLECONY';
			$sql_data['K_LOKALIZACJA'] = 'PROJEKT';
			$sql_data['K_LOKALIZACJA_NEW'] = 'PROJEKT';
		} else if ($task == 'rozwiazanie umowy') {
			$sql_zawartosc = 'rozwiazanie umowy ID ' . $params['nr_umowy'] . ' - ' . $params['powod_desc'];
			$sql_data['K_TYP_KORESP'] = 'IN';
		}
		if (!$sql_zawartosc) {
			return false;
		}

		/**
		 * przykładowe wezwania:
		 *   SELECT * FROM `IN7_DZIENNIK_KORESP` WHERE `K_ZAWARTOS` LIKE '%wezwanie%' ORDER BY `IN7_DZIENNIK_KORESP`.`ID` DESC;
		 * przykład:
		 *   SELECT * FROM `IN7_DZIENNIK_KORESP` WHERE `ID`=25951;
		 */

		$sql_data = array_merge($sql_data, [
			'A_STATUS' => 'WAITING',
			'ID_BILLING_USERS' => $user->ID,
			'K_DATA_OTRZYM_KORESP' => '', // data
			'K_DATA_OTRZYMANEJ_KORESP' => '', // data
			'K_OD_KOGO' => "{$user->P_NAME} {$user->P_NAME_SECOND}",
			'OD_KOGO_ADRES' => "ul. {$user->P_ADDRESS_STREET} {$user->P_ADDRESS_HOUSE}/{$user->P_ADDRESS_HOME}, {$user->P_ADDRESS_POST_CODE} {$user->P_ADDRESS_CITY}",
			'K_ZAWARTOS' => $sql_zawartosc,
			'K_ZNAK_REFERENTA' => User::getFullName() . ' (' . User::getInicjaly() . ')',
			'ID_PROJECT' => $id_proj,
			'A_RECORD_CREATE_DATE' => "NOW()",
			'A_RECORD_CREATE_AUTHOR' => User::getLogin(),
		]);

		$ret_id = DB::getPDO()->insert('IN7_DZIENNIK_KORESP', $sql_data);
		DB::getPDO()->insert('IN7_DZIENNIK_KORESP_HIST', array_merge($sql_data, [
			'ID_USERS2' => $ret_id,
		]));

		return $ret_id;
	}

	/**
	 * @param int $user_id - IS klienta
	 * @param int $id_deals_old - ID umowy do wyłączenia
	 * @param date 'Y-m-d' $termin_odlaczenia - Termin odłączenia
	 * @returns int - ID created record
	 */
	public static function create_deals_rozwiazanie($user_id, $id_deals_old, $termin_odlaczenia, $powod_desc = '') {
		$old_deal = DB::getPDO()->fetchFirst("
			select * from DEALS_TABLE where ID = :id
		", [ ':id' => $id_deals_old ]);
		if (!$old_deal) {
			return false;
		}

		$sql_data = [
			'DEALDESC' => "Rezygnacja z dniem {$termin_odlaczenia}",
			'A_STATUS' => 'WAITING',
			'ID_BILLING_USERS' => $user_id,
			'P_DEALNUMBER_OLD' => $old_deal->ID,
			'S_ADDRESS_STREET' => $old_deal->S_ADDRESS_STREET,
			'P_DEALDATE' => $old_deal->P_DEALDATE,
			'P_DEALDATE_TERM' => $old_deal->P_DEALDATE_TERM,
			'L2_HANGUP_FROM' => $termin_odlaczenia,
			'RODZAJ_DZIALANIA_HANDLOWEGO' => $powod_desc,
			// TODO: 'ID_OFFERS_OFF' => get_active_services_id($old_deal->ID),
		];

		$ret_id = DB::getPDO()->insert('DEALS_TABLE', $sql_data);
		DB::getPDO()->insert('DEALS_TABLE_HIST', array_merge($sql_data, [
			'ID_USERS2' => $ret_id,
		]));

		return $ret_id;
	}

}