se/SE/se-lib/ColumnDealsStatus.php

<?php


class ColumnDealsStatus {

	public static function get_table() {
		return 'temp_DEALS_STATUS';
	}

	public static function get_drop_query() {
		return "DROP TABLE IF EXISTS `" . self::get_table() . "`;";
	}

	public static function get_create_query() {
		return "CREATE TABLE IF NOT EXISTS `" . self::get_table() . "` (
			`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',
			`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;
		";
	}

	public static function get_update_query($userID = null) {
		$sqlUserID = '';
		if ($userID > 0) {
			$sqlUserID = " and `ID_BILLING_USERS`='{$userID}' ";
		}
		return "UPDATE `" . self::get_table() . "` 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 `" . self::get_table() . "` set
				`DEALS_BLOCKED`=1
			where `SERVICES_STATUS` like '%OFF_SOFT%'
				and `DEALS_ACTIVE`=0
				{$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 `" . self::get_table() . "` (`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();
		$_table = self::get_table();
		if (empty($_SESSION["{$_table}_COLUMN"]['_last_update'])) {
			$_SESSION["{$_table}_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["{$_table}_COLUMN"]['_last_update'] = $r->A_CREATE_DATE;
				}
			}
		}
		return $_SESSION["{$_table}_COLUMN"]['_last_update'];
	}

	public static function set_last_update( $date ) {
		$_SESSION[self::get_table().'_COLUMN']['_last_update'] = $date;
	}

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

	public static function run_update( $force = false, &$dbg_msgs = array() ) {
		$run = false;
		if ($force) {
			$run = true;
		} else {
			$run = ! self::is_updated();
		}
		if ($run) {
			$db = DB::getDB();

			$sql = self::get_drop_query();
			$dbg_msgs[] = $sql;
			$db->query($sql, false);

			$sql = self::get_create_query();
			$dbg_msgs[] = $sql;
			$db->query($sql, false);

			$sql = self::get_insert_query();
			$dbg_msgs[] = $sql;
			$db->query($sql, false);

			$sql = self::get_update_query();
			$dbg_msgs[] = $sql;
			$db->query($sql, false);

			$sql = self::get_update_blocked_query();
			$dbg_msgs[] = $sql;
			$db->query($sql, false);

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

	public static function run_update_for_user($userID) {
		$db = DB::getDB();
		$dbg_msgs = array();

		$_table = self::get_table();
		$sql = "delete from `{$_table}` where `ID_BILLING_USERS`='{$userID}'";
		$dbg_msgs[] = $sql;
		$db->query($sql, false);
		$dbg_msgs[] = "affected: " . $db->affected_rows();

		$sql = self::get_insert_query($userID);
		$dbg_msgs[] = $sql;
		$db->query($sql, false);
		$dbg_msgs[] = "affected: " . $db->affected_rows();

		$sql = self::get_update_query($userID);
		$dbg_msgs[] = $sql;
		$db->query($sql, false);
		$dbg_msgs[] = "affected: " . $db->affected_rows();

		$sql = self::get_update_blocked_query($userID);
		$dbg_msgs[] = $sql;
		$db->query($sql, false);
		$dbg_msgs[] = "affected: " . $db->affected_rows();

		IF(V::get('DBG_DEALS','',$_GET)){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;"> (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($dbg_msgs);echo'</pre>';}
		return true;
	}

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

		// read from cache if not force
		if (!$force) {
			if (!empty($_SESSION[self::get_table().'_COLUMN']['_stats_by_month_cache'])) {
				foreach ($_SESSION[self::get_table().'_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 `" . self::get_table() . "` 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 `" . self::get_table() . "` 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
		$_SESSION[self::get_table().'_COLUMN']['_stats_by_month_cache'] = array();
		foreach ($deals_stats as $k => $v) {
			$_SESSION[self::get_table().'_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;
	}

}