<?php


// sql table: 'temp_DEALS_STATUS'
class ColumnDealsStatus {

	function reinstall() {
		global $A_STATUS_L2_SQL;
		if (empty($A_STATUS_L2_SQL)) throw new Exception('Config Error: variable "$A_STATUS_L2_SQL" not exists!');

		DB::getPDO()->execSql(" DROP PROCEDURE IF EXISTS `update_deals_status` ");
		DB::getPDO()->execSql("
			CREATE DEFINER=`root`@`localhost` PROCEDURE `update_deals_status`()
			BEGIN
				DROP TABLE IF EXISTS `temp_DEALS_STATUS`;

				CREATE TABLE IF NOT EXISTS `temp_DEALS_STATUS` (
					`ID_DEALS` int(11) NOT NULL,
					`ID_BILLING_USERS` int(11) NOT NULL,
					`A_CREATE_DATE` date NOT NULL DEFAULT '0000-00-00',
					`DEALS_ACTIVE` int(1) NOT NULL DEFAULT '0',
					`DEALS_BLOCKED` int(1) NOT NULL DEFAULT '0',
					`P_DEALDATE_TERM` date NOT NULL DEFAULT '0000-00-00',
					`DEAL_TYPE` enum('UMOWA', 'ANEKS', 'INNE', '') NOT NULL DEFAULT '',
					`DATA_WYK_PODLACZENIA` date NOT NULL DEFAULT '0000-00-00',
					`SERVICES_STATUS` varchar(255) NOT NULL DEFAULT '',
					`SERVICES_S_ADDRESS_STREET` varchar(255) NOT NULL DEFAULT '',
					KEY `ID_DEALS` (`ID_DEALS`),
					KEY `ID_BILLING_USERS` (`ID_BILLING_USERS`),
					KEY `DEALS_ACTIVE` (`DEALS_ACTIVE`)
				) ENGINE=MyISAM DEFAULT CHARSET=latin2;

				INSERT INTO `temp_DEALS_STATUS` (`ID_DEALS`, `ID_BILLING_USERS`, `A_CREATE_DATE`, `SERVICES_STATUS`, `P_DEALDATE_TERM`, `SERVICES_S_ADDRESS_STREET`)
				select d.`ID`
					, d.`ID_BILLING_USERS`
					, NOW() as A_CREATE_DATE
					, GROUP_CONCAT({$A_STATUS_L2_SQL}) as SERVICES_STATUS
					, d.`P_DEALDATE_TERM`
					, GROUP_CONCAT( DISTINCT
							CASE SV.`NAME_LIST_SERVICES`
								WHEN 'USERS2' THEN (select `S_ADDRESS_STREET` from `SES_USERS2_A` as ses_a where ses_a.`ID_SERVICES`=SV.`ID`)
								WHEN 'TV' THEN (select `S_ADDRESS_STREET` from `SES_TV_A` as ses_a where ses_a.`ID_SERVICES`=SV.`ID`)
							END
						) as SERVICES_S_ADDRESS_STREET
				from `DEALS_TABLE` as d
					left join `SERVICES` as SV on (SV.`ID_DEALS_TABLE`=d.`ID`)
				where
					SV.`NAME_LIST_SERVICES` not in('EMAIL','WWW_MASS')
				group by d.`ID`;

				-- update DEALS_ACTIVE and DEALS_BLOCKED

				UPDATE `temp_DEALS_STATUS` set `DEALS_ACTIVE`  = 1 where `SERVICES_STATUS` like '%NORMAL%';

				UPDATE `temp_DEALS_STATUS` set `DEALS_BLOCKED` = 1 where `SERVICES_STATUS` like '%OFF_SOFT%' and `DEALS_ACTIVE` = 0;

				-- update DATA_WYK_PODLACZENIA `run_update_data_wyk_podlaczenia_query`

				UPDATE `temp_DEALS_STATUS` t
					join `DEALS_TABLE` d on ( d.ID = t.ID_DEALS )
				set t.`DEAL_TYPE` = 'UMOWA'
				where t.`DEALS_ACTIVE` = 1
					and d.ID_OFFERS_ON != '' and d.ID_OFFERS_OFF = ''
				;
				UPDATE `temp_DEALS_STATUS` t
					join `DEALS_TABLE` d on ( d.ID = t.ID_DEALS )
				set t.`DEAL_TYPE` = 'ANEKS'
				where t.`DEALS_ACTIVE` = 1
					and d.ID_OFFERS_ON != '' and d.ID_OFFERS_OFF != ''
				;
				UPDATE `temp_DEALS_STATUS` t
					join `DEALS_TABLE` d on ( d.ID = t.ID_DEALS )
				set t.`DEAL_TYPE` = 'INNE'
				where t.`DEALS_ACTIVE` = 1
					and t.DEAL_TYPE not in ( 'UMOWA', 'ANEKS' )
				;
				UPDATE `temp_DEALS_STATUS` t
					join (
						(
							select a.T_WHEN_CONNECTED, s.ID_DEALS_TABLE, a.ID_BILLING_USERS
							from SES_USERS2_A a
								join SERVICES s on ( s.ID = a.ID_SERVICES )
							where a.T_WHEN_CONNECTED > 0
						)
						union
						(
							select a.T_WHEN_CONNECTED, s.ID_DEALS_TABLE, a.ID_BILLING_USERS
							from SES_TV_A a
								join SERVICES s on ( s.ID = a.ID_SERVICES )
							where a.T_WHEN_CONNECTED > 0
						)
						union
						(
							select a.T_WHEN_CONNECTED, s.ID_DEALS_TABLE, a.ID_BILLING_USERS
							from SES_VOIP_A a
								join SERVICES s on ( s.ID = a.ID_SERVICES )
							where a.T_WHEN_CONNECTED > 0
						)
						order by T_WHEN_CONNECTED asc
						limit 1
					) x on ( x.ID_BILLING_USERS = t.ID_BILLING_USERS and x.ID_DEALS_TABLE = t.ID_DEALS )
				set t.`DATA_WYK_PODLACZENIA` = x.T_WHEN_CONNECTED
				where t.`DEALS_ACTIVE` = 1
					and t.`DEAL_TYPE` = 'UMOWA'
				;
				UPDATE `temp_DEALS_STATUS` t
					join `DEALS_TABLE` d on ( d.ID = t.ID_DEALS )
				set t.`DATA_WYK_PODLACZENIA` = IF( d.USTALONA_DATA_PODLACZENIA > 0, d.USTALONA_DATA_PODLACZENIA, d.P_DEALDATE )
				where t.`DEALS_ACTIVE` = 1
					and t.`DEAL_TYPE` = 'ANEKS'
				;
				UPDATE `DEALS_TABLE` d
					join `temp_DEALS_STATUS` t on ( d.ID = t.ID_DEALS )
				set d.`DATA_WYK_PODLACZENIA` = t.DATA_WYK_PODLACZENIA
				where t.`DEALS_ACTIVE` = 1
				;

			END
		");
		DB::getPDO()->execSql("
			CREATE EVENT IF NOT EXISTS `_DEALS_TABLE__update_deals_status_every_day_event`
				ON SCHEDULE EVERY 1 DAY STARTS '2019-01-01 04:00:00'
				ON COMPLETION NOT PRESERVE ENABLE
			DO BEGIN
				call `update_deals_status`();
			END
		");
	}


	public static function get_update_query($userID = null) {
		$sqlUserID = '';
		if ($userID > 0) {
			$sqlUserID = " and `ID_BILLING_USERS` = '{$userID}' ";
		}
		return "
			UPDATE `temp_DEALS_STATUS` set
				`DEALS_ACTIVE` = 1
			where `SERVICES_STATUS` like '%NORMAL%'
				{$sqlUserID}
		";
	}

	public static function get_update_blocked_query($userID = null) {
		$sqlUserID = '';
		if ($userID > 0) {
			$sqlUserID = " and `ID_BILLING_USERS` = '{$userID}' ";
		}
		return "
			UPDATE `temp_DEALS_STATUS` set
				`DEALS_BLOCKED`=1
			where `SERVICES_STATUS` like '%OFF_SOFT%'
				and `DEALS_ACTIVE` = 0
				{$sqlUserID}
		";
	}

	public static function run_update_data_wyk_podlaczenia_query($userID = null) {
		$sqlUserID = '';
		if ($userID > 0) {
			$sqlUserID = " and t.`ID_BILLING_USERS` = '{$userID}' ";
		}
		DB::getPDO()->execSql("
			UPDATE `temp_DEALS_STATUS` t
				join `DEALS_TABLE` d on ( d.ID = t.ID_DEALS )
			set t.`DEAL_TYPE` = 'UMOWA'
			where t.`DEALS_ACTIVE` = 1
				and d.ID_OFFERS_ON != '' and d.ID_OFFERS_OFF = ''
				{$sqlUserID}
		");
		DB::getPDO()->execSql("
			UPDATE `temp_DEALS_STATUS` t
				join `DEALS_TABLE` d on ( d.ID = t.ID_DEALS )
			set t.`DEAL_TYPE` = 'ANEKS'
			where t.`DEALS_ACTIVE` = 1
				and d.ID_OFFERS_ON != '' and d.ID_OFFERS_OFF != ''
				{$sqlUserID}
		");
		DB::getPDO()->execSql("
			UPDATE `temp_DEALS_STATUS` t
				join `DEALS_TABLE` d on ( d.ID = t.ID_DEALS )
			set t.`DEAL_TYPE` = 'INNE'
			where t.`DEALS_ACTIVE` = 1
				and t.DEAL_TYPE not in ( 'UMOWA', 'ANEKS' )
				{$sqlUserID}
		");
		// DB::getPDO()->execSql("
		// 	UPDATE `temp_DEALS_STATUS` t
		// 	set t.`DATA_WYK_PODLACZENIA` = (
		// 			select a.T_WHEN_CONNECTED
		// 			from SES_USERS2_A a
		// 				join SERVICES s on ( s.ID = a.ID_SERVICES )
		// 			where a.ID_BILLING_USERS = t.ID_BILLING_USERS
		// 				and s.ID_DEALS_TABLE = t.ID_DEALS
		// 				and a.T_WHEN_CONNECTED > 0
		// 			order by a.T_WHEN_CONNECTED asc
		// 			limit 1
		// 		)
		// 	where t.`DEALS_ACTIVE` = 1
		// 		and t.`DEAL_TYPE` = 'UMOWA'
		// 		{$sqlUserID}
		// ");
		DB::getPDO()->execSql("
			UPDATE `temp_DEALS_STATUS` t
				join (
					(
						select a.T_WHEN_CONNECTED, s.ID_DEALS_TABLE, a.ID_BILLING_USERS
						from SES_USERS2_A a
							join SERVICES s on ( s.ID = a.ID_SERVICES )
						where a.T_WHEN_CONNECTED > 0
					)
					union
					(
						select a.T_WHEN_CONNECTED, s.ID_DEALS_TABLE, a.ID_BILLING_USERS
						from SES_TV_A a
							join SERVICES s on ( s.ID = a.ID_SERVICES )
						where a.T_WHEN_CONNECTED > 0
					)
					union
					(
						select a.T_WHEN_CONNECTED, s.ID_DEALS_TABLE, a.ID_BILLING_USERS
						from SES_VOIP_A a
							join SERVICES s on ( s.ID = a.ID_SERVICES )
						where a.T_WHEN_CONNECTED > 0
					)
					order by T_WHEN_CONNECTED asc
					limit 1
				) x on ( x.ID_BILLING_USERS = t.ID_BILLING_USERS and x.ID_DEALS_TABLE = t.ID_DEALS )
			set t.`DATA_WYK_PODLACZENIA` = x.T_WHEN_CONNECTED
			where t.`DEALS_ACTIVE` = 1
				and t.`DEAL_TYPE` = 'UMOWA'
				{$sqlUserID}
		");
		DB::getPDO()->execSql("
			UPDATE `temp_DEALS_STATUS` t
				join `DEALS_TABLE` d on ( d.ID = t.ID_DEALS )
			set t.`DATA_WYK_PODLACZENIA` = IF( d.USTALONA_DATA_PODLACZENIA > 0, d.USTALONA_DATA_PODLACZENIA, d.P_DEALDATE )
			where t.`DEALS_ACTIVE` = 1
				and t.`DEAL_TYPE` = 'ANEKS'
				{$sqlUserID}
		");

		DB::getPDO()->execSql("
			UPDATE `DEALS_TABLE` d
				join `temp_DEALS_STATUS` t on ( d.ID = t.ID_DEALS )
			set d.`DATA_WYK_PODLACZENIA` = t.DATA_WYK_PODLACZENIA
			where t.`DEALS_ACTIVE` = 1
				{$sqlUserID}
		");
	}

	public static function get_insert_query($userID = null) {
		global $A_STATUS_L2_SQL;// @from .config.php
		if (empty($A_STATUS_L2_SQL)) {
			die('Config Error: variable "$A_STATUS_L2_SQL" not exists!');
		}
		/*
			select d.`ID`
							, d.`ID_BILLING_USERS`
							, '2012-10-01' as A_CREATE_DATE
							, GROUP_CONCAT(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'
						)
						)
						)
						)
						)
					 )
					)
				 )
			 ) as SERVICES_STATUS
				, d.`P_DEALDATE_TERM`
			from `DEALS_TABLE` as d
				left join `SERVICES` as SV on (SV.`ID_DEALS_TABLE`=d.`ID`)
			where
				SV.`NAME_LIST_SERVICES` not in('EMAIL')
			group by d.`ID`
		 */
		// TODO: dodac adresy z pozostalych uslug VOIP, TVCP, etc...
		$sqlUserID = '';
		if ($userID > 0) {
			$sqlUserID = " and SV.`ID_BILLING_USERS`='{$userID}' ";
		}
		return "
			INSERT INTO `temp_DEALS_STATUS` (`ID_DEALS`, `ID_BILLING_USERS`, `A_CREATE_DATE`, `SERVICES_STATUS`, `P_DEALDATE_TERM`, `SERVICES_S_ADDRESS_STREET`)
			select d.`ID`
				, d.`ID_BILLING_USERS`
				, '" . date("Y-m-d") . "' as A_CREATE_DATE
				, GROUP_CONCAT({$A_STATUS_L2_SQL}) as SERVICES_STATUS
				, d.`P_DEALDATE_TERM`
				, GROUP_CONCAT( DISTINCT
						CASE SV.`NAME_LIST_SERVICES`
							WHEN 'USERS2' THEN (select `S_ADDRESS_STREET` from `SES_USERS2_A` as ses_a where ses_a.`ID_SERVICES`=SV.`ID`)
							WHEN 'TV' THEN (select `S_ADDRESS_STREET` from `SES_TV_A` as ses_a where ses_a.`ID_SERVICES`=SV.`ID`)
						END
					) as SERVICES_S_ADDRESS_STREET
			from `DEALS_TABLE` as d
				left join `SERVICES` as SV on (SV.`ID_DEALS_TABLE`=d.`ID`)
			where
				SV.`NAME_LIST_SERVICES` not in('EMAIL','WWW_MASS')
				{$sqlUserID}
			group by d.`ID`
		";
	}

	public static function get_last_update() {
		$db = DB::getDB();
		$tableName = 'temp_DEALS_STATUS';
		if (empty($_SESSION["{$tableName}_COLUMN"]['_last_update'])) {
			$_SESSION["{$tableName}_COLUMN"]['_last_update'] = '';
			$sql = "SELECT td.`A_CREATE_DATE` from `temp_DEALS_STATUS` as td limit 1";
			$res = $db->query($sql, false);
			if ($res) {
				if ($r = $db->fetch($res)) {
					$_SESSION["{$tableName}_COLUMN"]['_last_update'] = $r->A_CREATE_DATE;
				}
			}
		}
		return $_SESSION["{$_table}_COLUMN"]['_last_update'];
	}

	public static function set_last_update( $date ) {
		$tableName = 'temp_DEALS_STATUS';
		$_SESSION["{$tableName}_COLUMN"]['_last_update'] = $date;
	}

	public static function is_updated() {
		return (self::get_last_update() >= date("Y-m-d"));
	}

	public static function run_update( $force = false ) {
		$run = ($force) ? true : !self::is_updated();
		if ($run) {
			DB::getPDO()->execSql(" DROP TABLE IF EXISTS `temp_DEALS_STATUS` ");

			DB::getPDO()->execSql("
				CREATE TABLE IF NOT EXISTS `temp_DEALS_STATUS` (
					`ID_DEALS` int(11) NOT NULL,
					`ID_BILLING_USERS` int(11) NOT NULL,
					`A_CREATE_DATE` date NOT NULL DEFAULT '0000-00-00',
					`DEALS_ACTIVE` int(1) NOT NULL DEFAULT '0',
					`DEALS_BLOCKED` int(1) NOT NULL DEFAULT '0',
					`P_DEALDATE_TERM` date NOT NULL DEFAULT '0000-00-00',
					`DEAL_TYPE` enum('UMOWA', 'ANEKS', 'INNE', '') NOT NULL DEFAULT '',
					`DATA_WYK_PODLACZENIA` date NOT NULL DEFAULT '0000-00-00',
					`SERVICES_STATUS` varchar(255) NOT NULL DEFAULT '',
					`SERVICES_S_ADDRESS_STREET` varchar(255) NOT NULL DEFAULT '',
					KEY `ID_DEALS` (`ID_DEALS`),
					KEY `ID_BILLING_USERS` (`ID_BILLING_USERS`),
					KEY `DEALS_ACTIVE` (`DEALS_ACTIVE`)
				) ENGINE=MyISAM DEFAULT CHARSET=latin2;
			");

			$sql = self::get_insert_query();
			DB::getPDO()->execSql($sql);

			$sql = self::get_update_query();
			DB::getPDO()->execSql($sql);

			$sql = self::get_update_blocked_query();
			DB::getPDO()->execSql($sql);

			self::run_update_data_wyk_podlaczenia_query();

			$date = date("Y-m-d");
			self::set_last_update($date);
		}
		return true;
	}

	public static function run_update_for_user($userID) {
		DB::getPDO()->execSql(" DELETE from `temp_DEALS_STATUS` where `ID_BILLING_USERS` = :id_user ", [ ':id_user' => $userID ]);

		$sql = self::get_insert_query($userID);
		DB::getPDO()->execSql($sql);

		$sql = self::get_update_query($userID);
		DB::getPDO()->execSql($sql);

		$sql = self::get_update_blocked_query($userID);
		DB::getPDO()->execSql($sql);

		$sql = self::run_update_data_wyk_podlaczenia_query($userID);
		DB::getPDO()->execSql($sql);

		return true;
	}

	public static function get_deals_stats_by_months( $force = false ) {
		$deals_stats = array();

		// read from cache if not force
		if (!$force) {
			$tableName = 'temp_DEALS_STATUS';
			if (!empty($_SESSION["{$tableName}_COLUMN"]['_stats_by_month_cache'])) {
				foreach ($_SESSION["{$tableName}_COLUMN"]['_stats_by_month_cache'] as $k => $v) {
					$deals_stats[$k] = $v;
				}
				return $deals_stats;
			}
		}

		$loop_limit = 12;

		// fetch months, count
		$sql = "
			select
				IF(substr(td.`P_DEALDATE_TERM`, 1, 7) < '" . date('Y-m') . "-00'
					, '0000-00'
					, substr(td.`P_DEALDATE_TERM`, 1, 7)
				) as deal_term_month
				, count(1) as cnt
			from `temp_DEALS_STATUS` as td
			where td.`DEALS_ACTIVE`=1
			group by deal_term_month
			order by deal_term_month asc
		";
		if (V::get('DBG_DEALS', '', $_GET)){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = DB::query( $sql );
		while ($r = DB::fetch_assoc( $res )) {
			$r['suma'] = 0;
			$deals_stats[$r['deal_term_month']] = $r;
		}

		// fetch suma, for first 12 months
		foreach ($deals_stats as $k_month => $v_month) {
			if ($loop_limit-- <= 0) {
				break;
			}
			$db = DB::getDB();
			$sqlWhereTerm = "td.`P_DEALDATE_TERM` like '" . $k_month . "-%'";
			if ($k_month == '0000-00') {
				$sqlWhereTerm = "substr(td.`P_DEALDATE_TERM`, 1, 7)<'" . date('Y-m') . "-00'";
			}
			$sql = "
				select
					sum( (u.`ABONAMENT_VALUE` * (100 + u.`VAT`))/100 ) as suma
				from `temp_DEALS_STATUS` as td
					left join `SERVICES` as u on (u.`ID_DEALS_TABLE`=td.`ID_DEALS`)
				where
					{$sqlWhereTerm}
					and td.`DEALS_ACTIVE`=1
					and u.`ID` is not null
			";
			if (V::get('DBG_DEALS', '', $_GET)){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
			$res = $db->query($sql);
			if ($r = $db->fetch($res)) {
				if (isset($deals_stats[$k_month])) {
					$deals_stats[$k_month]['suma'] = $r->suma;
				}
			}
		}

		if (V::get('DBG_DEALS', '', $_GET)){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">deals_stats (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($deals_stats);echo'</pre>';}
		// save in cache
		$tableName = 'temp_DEALS_STATUS';
		$_SESSION["$tableName}_COLUMN"]['_stats_by_month_cache'] = array();
		foreach ($deals_stats as $k => $v) {
			$_SESSION["$tableName}_COLUMN"]['_stats_by_month_cache'][$k] = $v;
		}

		return $deals_stats;
	}

	public static function get_deals_stats_by_month($_month, $_limitstart) {
		$sql_where = "";
		$sql_limit = "limit 100";
		if ($_limitstart > 0) {
			$sql_limit .= " offset " . $_limitstart;
		}

		$sql_where_and_arr = array();

		if ($_month == '0000-00') {// filter - P_DEALDATE_TERM
			$sql_where_and_arr[] = "td.`P_DEALDATE_TERM`<'" . date("Y-m") . "-00'";
		} else {
			$sql_where_and_arr[] = "td.`P_DEALDATE_TERM` like '" . $_month . "-%'";
		}
		$sql_where_and_arr[] = "td.`DEALS_ACTIVE`=1";// filter - ACTIVE: tylko aktywne umowy


		$sql_where = (!empty($sql_where_and_arr))? "where" . "\n" . implode(" and ", $sql_where_and_arr) : "";
		$db = DB::getDB();
		$sql = "select
				d.`ID`
				, d.`ID_BILLING_USERS`
				, d.`P_DEALPREFIX`
				, d.`P_DEALNUMBER`
				, d.`P_DEALNUMBER_OLD`
				, d.`P_DEALDATE`
				, d.`P_DEALDATE_TERM`
				, d.`ANEX_NEEDED`
				, d.`ANEX_CONFIRM_DATE`
				, d.`DEALDESC`
				, d.`A_STATUS`
				, BUA.`P_PHONE` as BUA_P_PHONE
				, concat_ws(' ', BUA.`P_NAME`, BUA.`P_NAME_SECOND`) as BUA_P_NAME
			from `temp_DEALS_STATUS` as td
				left join `DEALS_TABLE` as d on (d.`ID`=td.`ID_DEALS`)
				left join `BILLING_USERS_ADD` as BUA on (BUA.`id_users`=d.`ID_BILLING_USERS`)
			" . $sql_where . "
			order by d.`ID` ASC
			" . $sql_limit . "
		";
		if (V::get('DBG_DEALS', '', $_GET)){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$deals[$r->ID] = $r;
		}
		if (empty($deals)) {
			return $deals;
		}

//	function fetch_services(&$deals)
		$sql_deals_ids = array();
		foreach ($deals as $k_id => $v_deal) {
			$sql_deals_ids[] = "'" . $k_id . "'";
			$deals[$k_id]->uslugi = array();
		}
		if (empty($sql_deals_ids)) {
			return $deals;
		}

		global $A_STATUS_L2_SQL;// @from .config.php, @require SERVICES as SV
		$db = DB::getDB();
		$sql = "select
					SV.`ID`
					, SV.`ID_DEALS_TABLE`
					, $A_STATUS_L2_SQL as A_STATUS
					, SV.`ID_OFFERS`
					, SV.`ABONAMENT_VALUE`
					, SV.`NAME_LIST_SERVICES`
					, SV.`VAT`
					, IF(SV.`NAME_LIST_SERVICES`='USERS2'
							, (select `S_ADDRESS_STREET` from `SES_USERS2_A` as ses_a where ses_a.`ID_SERVICES`=SV.`ID`)
							, IF(SV.`NAME_LIST_SERVICES`='TV'
									, (select `S_ADDRESS_STREET` from `SES_TV_A` as ses_a where ses_a.`ID_SERVICES`=SV.`ID`)
									, null
								)
						) as S_ADDRESS_STREET
			from `SERVICES` as SV
			where SV.`ID_DEALS_TABLE` in(" . implode(", ", $sql_deals_ids) . ")
		";
		if (V::get('DBG_DEALS', '', $_GET)){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$deals[$r->ID_DEALS_TABLE]->uslugi[$r->ID] = $r;
		}
//	} // fetch_services

		return $deals;
	}

}