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();
		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 = DB::_($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::_($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::_($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::_($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 = '') {
		$total = 0;
		$sql_where = self::_parse_query($q);
		$db = DB::getDB();
		$sql = "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}
		";
		//echo'<pre>sqlTotal:';print_r($sql);echo'</pre>';
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$total = $r->cnt;
		}
		return $total;
	}

	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}";
			}
		}
		$db = DB::getDB();
		$sql = "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}
		";
		DBG::_('DBG_SQL', '>1', "sql", $sql, __CLASS__, __FUNCTION__, __LINE__);
		//echo'<pre>';print_r($sql);echo'</pre>';
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$ret[$r->id_users] = $r;
		}
		return $ret;
	}

	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();
		$sql_where = "a.`id_users`='{$id}'";
		$db = DB::getDB();
		$sql = "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}
				{$sqlWhereAdd}
		";
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			if(0){
				$r->BA_WINIEN = "";
				$r->BA_MA = "";
				$r->BA_TIMESTAMP = "";
				$sql = "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`='" . $r->ID . "'
				";
				$db_webone = DB::getDB('931');
				if ($db_webone) {
					$res_fin = $db_webone->query($sql);
					if ($r_fin = $db_webone->fetch($res_fin)) {
						$r->BA_WINIEN = $r_fin->BA_WINIEN;
						$r->BA_MA = $r_fin->BA_MA;
						$r->BA_TIMESTAMP = $r_fin->BA_TIMESTAMP;
					}
				}
			}
			$ret = $r;
		}
		return $ret;
	}

	public static function get_company(&$user) {
		if (!isset($user->_company)) {
			$db = DB::getDB();
			/*
				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
			 */
			$sql = "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`='{$user->ID_BILLING_USERS}'
			";
			//$db_webone = DB::getDB('931');
			//if ($db_webone) {
				//$res = $db_webone->query($sql);
				//if ($r = $db_webone->fetch($res)) {
				//	$user->_company = $r;
				//}
			//}
			$res = $db->query($sql);
			if ($r = $db->fetch($res)) {
				$user->_company = $r;
			}
		}
		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();
			$db_webone = DB::getDB('931');
			if ($db_webone) {
				$sql_where = "1=1";
				if (!empty($ids)) {
					$sql_where = "`ID` in ('".implode("','", $ids)."')";
				}
				$sql = "select *
					from `BILLING_NUMBERS_TYPE`
					where
						{$sql_where}
				";
				$res = $db_webone->query($sql);
				while ($h = $db_webone->fetch_assoc($res)) {
					$billing_types[$h['ID']] = $h;
				}
			}
			$_cache[$cache_key] = $billing_types;
		}
		return $_cache[$cache_key];
	}

	public static function getAllServicesStreetsCSV($user) {
		$streets = array();
		$db = DB::getDB();
		$sql = "
			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`='{$user->ID}'
		--		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')
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$streets[$r->S_ADDRESS_STREET] = true;
		}
		$streets = (!empty($streets))? implode(',', array_keys($streets)) : '';
		return $streets;
	}

	public static function get_umowy_from_l2($user) {
		$ret = array();
		$db_webone = DB::getDB();
		if (!$db_webone) {
			return -1;
		}

		$sql = "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`='{$user->ID}'
			order by d.`ID` DESC
		";
		$res = $db_webone->query($sql);
		while ($h = $db_webone->fetch_assoc($res)) {
			$ret[$h['ID']] = $h;
		}
		return $ret;
	}

	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);
		$uID = $user->ID;

		$ret = array();
		$db_webone = DB::getDB();
		if (!$db_webone) {
			return -1;
		}

		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] => 
		}
		$sql = "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`='{$user->ID}'
		";
		$res = $db_webone->query($sql);
		while ($h = $db_webone->fetch_assoc($res)) {
			$ret[$h['ID']] = $h;
		}
		return $ret;
	}

	public static function get_umowy_from_l3(&$user) {
		$ret = array();
		$db = DB::getDB();
		$sql = "select d.*
			from `USERS2_DEALS` as d
			where
				(d.`ID_BILLING_USERS_OLD`='{$user->ID}' or d.`ID_BILLING_USERS_NEW`='{$user->ID}')
			order by d.`ID` DESC
		";
		$res = $db->query($sql);
		while ($h = $db->fetch_assoc($res)) {
			$ret[] = $h;
		}
		return $ret;
	}

	public static function get_bill_dosc_by_date(&$user, $date_limit = null) {
		// fetch finanse data from remote DB
		$billing_docs = new Windykacja_BillingListDocs();
		$db_webone = DB::getDB('931');
		if (!$db_webone) {
			echo'<p style="color:red">'."Blad polaczenia do bazy dancyh webone_billing - Zasob [931]".'</p>';
			return;
		}
		$sql_where = " BILLS_FVAT.OPEN='N' and  BILLS_FVAT.ID_BILLING_USERS ='{$user->ID}' ";
		// blad w zaokragleniach
		//		, sum((BILLS_FVAT_POS.AMMOUNT * BILLS_FVAT_POS.PRICE)*(1 + BILLS_FVAT_POS.VAT/100)) as WARTOSC
		if ($date_limit) {
			$sql_where .= " and BILLS_FVAT.PAYMENT_TERM <= '{$date_limit}' ";
		}
		$sql = "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 {$sql_where}
			group by BILLS_FVAT.ID
			order by BILLS_FVAT.ID_BILLING_NUMBERS ASC
		";
		//echo'<pre>sql1: L.' . __LINE__ . "\n";print_r($sql);echo'</pre>';
//echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">';print_r($sql);echo'</pre>';
		$res = $db_webone->query($sql);
		while ($h = $db_webone->fetch_assoc($res)) {
			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}
				";
				//echo'<pre>sql2: L.' . __LINE__ . "\n";print_r($sql);echo'</pre>';
				$res = $db_webone->query($sql);
				while ($h = $db_webone->fetch_assoc($res)) {
					$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`
				";
				//echo'<pre>sql3: L.' . __LINE__ . "\n";print_r($sql);echo'</pre>';
				$res = $db_webone->query($sql);
				while ($h = $db_webone->fetch_assoc($res)) {
					$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;

		$db_webone = DB::getDB('931');
		if (!$db_webone) {
			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_webone->query("drop TEMPORARY table TEMP_BILLING_ACCOUNTS_FILES");
		$result_create = $db_webone->query($sql);
		$result_alter = $db_webone->query($sql_alter);
		$result_create2 = $db_webone->query($sql2);
echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">db_webone: ';print_r($db_webone);echo'</pre>';

		$sql = "select * from TEMP_BILLING_ACCOUNTS_FILES  order by ID_BILLING_USERS,ID ";
		$sql_count = "select count(1) as cnt from TEMP_BILLING_ACCOUNTS_FILES ";
		$count = 0;
		$res = $db_webone->query($sql_count);
		if ($r_count = $db_webone->fetch($res)) {
			$count = $r_count->cnt;
		}
echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">count: ';print_r($count);echo'</pre>';
		if ($count > 0 && $ID_BILLING_USERS_PARAM > 0) {
			$result = $db_webone->query($sql);
		} else {
			$sql_none = "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_BILLING_USERS_PARAM}' and TILL=YEAR(NOW())-1
			";
			$db_webone->query($sql_none);
			$result = $db_webone->query($sql);
		}
		while ($h_baf = $db_webone->fetch_row($result)) {
			$baf[] = $h_baf;
		}
		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)) {
			$db_webone = DB::getDB('931');
			if (!$db_webone) {
				return -1;
			}

			$sql = "select `ID`, `NUMBER`, `ID_BILLING_PREFIXES`
				from `BILLING_NUMBERS`
				where
					`ID` in(".implode(",", $fetch_ids).")
			";
			$res = $db_webone->query($sql);
			$bnum = array();
			while ($h = $db_webone->fetch_assoc($res)) {
				$bnum[$h['ID']] = $h;
			}
			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;
 */

		$db = DB::getDB();
		$sql_obj = new stdClass();
		$sql_obj->A_STATUS = 'WAITING';
		foreach ($sql_data as $k_field => $v_value) {
			$sql_obj->$k_field = $v_value;
		}
		$sql_obj->ID_BILLING_USERS = $user->ID;
		$sql_obj->K_DATA_OTRZYM_KORESP = '';// data ?
		$sql_obj->K_DATA_OTRZYMANEJ_KORESP = '';// data ?
		$sql_obj->K_OD_KOGO = "{$user->P_NAME} {$user->P_NAME_SECOND}";
		$sql_obj->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}";
		$sql_obj->K_ZAWARTOS = $sql_zawartosc;
		$sql_obj->K_ZNAK_REFERENTA = User::getFullName() . ' (' . User::getInicjaly() . ')';
		$sql_obj->ID_PROJECT = $id_proj;
		$ret_id = $db->ADD_NEW_OBJ('IN7_DZIENNIK_KORESP', $sql_obj);
		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 = '') {
		$db = DB::getDB();
		$old_deal = $db->get_by_id('DEALS_TABLE', $id_deals_old);
		if (!$old_deal) {
			return false;
		}

		$sql_obj = new stdClass();
		$sql_obj->DEALDESC = "Rezygnacja z dniem {$termin_odlaczenia}";
		$sql_obj->A_STATUS = 'WAITING';
		$sql_obj->ID_BILLING_USERS = $user_id;
		$sql_obj->P_DEALNUMBER_OLD = $old_deal->ID;
		$sql_obj->S_ADDRESS_STREET = $old_deal->S_ADDRESS_STREET;
		$sql_obj->P_DEALDATE = $old_deal->P_DEALDATE;
		$sql_obj->P_DEALDATE_TERM = $old_deal->P_DEALDATE_TERM;
		$sql_obj->L2_HANGUP_FROM = $termin_odlaczenia;
		$sql_obj->RODZAJ_DZIALANIA_HANDLOWEGO = $powod_desc;
		// TODO: $sql_obj->ID_OFFERS_OFF = get_active_services_id($old_deal->ID);
/*
		if(
			SV.HANGUP_STATUS=SV.A_STATUS
			, coalesce(SV.A_STATUS)
			, if(
				( unix_timestamp(NOW()) > unix_timestamp(SV.HANGUP_FROM)
					and  ( SV.HANGUP_TILL='0000-00-00' or SV.HANGUP_TILL is NULL )
					and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )
				)
				, coalesce(SV.HANGUP_STATUS)
				, if(
					( unix_timestamp(NOW()) < unix_timestamp(SV.HANGUP_FROM) 
						and (SV.HANGUP_TILL='0000-00-00' or SV.HANGUP_TILL is NULL )
						and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )
					)
					, coalesce(SV.A_STATUS)
					, if(
						( unix_timestamp(NOW()) > unix_timestamp(SV.HANGUP_FROM)
							and unix_timestamp(NOW()) < unix_timestamp(SV.HANGUP_TILL)
							and ( SV.HANGUP_TILL<>'0000-00-00' and SV.HANGUP_TILL is not NULL )
							and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )
						)
					, coalesce(SV.HANGUP_STATUS)
					, if(
							( unix_timestamp(NOW()) < unix_timestamp(SV.HANGUP_FROM) and  unix_timestamp(NOW())< unix_timestamp(SV.HANGUP_TILL) 
			and  ( SV.HANGUP_TILL<>'0000-00-00' and SV.HANGUP_TILL is not NULL )
			and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null ))
							, coalesce(SV.A_STATUS)
							, if(
									(	( SV.HANGUP_TILL='0000-00-00' or SV.HANGUP_TILL is NULL )
			and ( SV.HANGUP_FROM='0000-00-00' or SV.HANGUP_FROM is null ))
									, coalesce(SV.A_STATUS)
									, if( unix_timestamp(NOW()) > unix_timestamp(SV.HANGUP_FROM)
											and  unix_timestamp(NOW()) > unix_timestamp(SV.HANGUP_TILL)
											and (
												( SV.HANGUP_TILL<>'0000-00-00' and SV.HANGUP_TILL is not NULL )
												and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )
											)
										, coalesce(SV.A_STATUS)
										, if(
											unix_timestamp(NOW()) > unix_timestamp(SV.HANGUP_FROM)
											and unix_timestamp(NOW()) > unix_timestamp(SV.HANGUP_TILL)
											and SV.HANGUP_FROM<SV.HANGUP_TILL
											and (
			( SV.HANGUP_TILL<>'0000-00-00' and SV.HANGUP_TILL is not NULL )
			and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )) , 'ERROR1', 
			'ERROR2'
			)
			)      
			)    
			)
			)
			)    
			)
		)
 */
/*
		$id_offers_off = array();
		$sql = "select
				s.``
				, s.`ID_OFFERS`
			from `SERVICES` as s
			where
				s.`ID_DEALS_TABLE`='{$old_deal->ID}'
				and s.`ID_BILLING_USERS`='{$user_id}'
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$id_offers_off[] = $r;
		}
 */
		$ret_id = $db->ADD_NEW_OBJ('DEALS_TABLE', $sql_obj);
		return $ret_id;
	}

}