se/SE/superedit-ANALIZA_GRUP_KOSZTOW.php

<?php

if (!defined('DS')) define('DS', DIRECTORY_SEPARATOR);
if (!defined('APP_PATH_ROOT')) define('APP_PATH_ROOT', dirname(__FILE__));
if (!defined('APP_PATH_WWW')) define('APP_PATH_WWW', dirname(__FILE__));
if (!defined('APP_PATH_CONFIG')) define('APP_PATH_CONFIG', APP_PATH_ROOT . DS . 'config');

function ANALIZA_GRUP_KOSZTOW() {

	require_once dirname(__FILE__) . '/' . 'se-lib' . '/' . 'Lib.php';
	Lib::loadClass('V');
	Lib::loadClass('User');
	Lib::loadClass('Config');
	Lib::loadClass('DB');

	$analizaGrupKosztowRouter = new AnalizaGrupKosztowRouter();

	$_print = V::get('_print', '', $_GET);
	if (!$_print) {
		$analizaGrupKosztowRouter->menu();
	}

	$task = V::get('task', 'cyfrowe', $_GET);
	$grupyKosztow = $analizaGrupKosztowRouter->getGrupyKosztow();

	$projekty = $analizaGrupKosztowRouter->getProjekty();
	$costs = $analizaGrupKosztowRouter->getCosts();

	$orderby = V::get('orderby', '', $_GET);
	$view_type = V::get('view_type', '', $_GET);
	$analizaGrupKosztowRouter->cyfrowe($projekty, $grupyKosztow, $costs, $orderby, $view_type);
}

class AnalizaGrupKosztowRouter {

	// TODO: function link()

	function menu() {
		// TODO: MENU_INIT na FUNCTION_INIT i HEADER NOT INIT
		?>
		<div>
		</div>
		<?php
	}

	function css() {
		?>
	<style type="text/css">
/* Styles for rotateTableCellContent plugin */
#oferta-programowa-cyfrowe div.rotated {
  -webkit-transform:rotate(-90deg);
  -moz-transform:rotate(-90deg);
	-o-transform:rotate(-90deg);
  writing-mode:tb-rl;
  white-space:nowrap;
}

body{font-family:arial}

.oferta-programowa { border-collapse:collapse; border:1px solid #7EC5FF; }
.oferta-programowa td { border:1px solid #7EC5FF; }
.oferta-programowa .prog-group { color:#7A7A7A; }
.oferta-programowa .cyfrowe-prog-count {}
.oferta-programowa .p2 { padding:0 2px; }
.oferta-programowa .c { text-align:center; }
.oferta-programowa .r { text-align:right; }
.oferta-programowa .cyfrowe-prog-count td { background-color:#fff; }
.oferta-programowa .hd { background-color:#FF5F5F; color:#fff; font-weight:bold; }
.oferta-programowa .nr { color:#7A7A7A; }
.oferta-programowa thead th { border:1px solid #7EC5FF; }

#oferta-programowa-cyfrowe thead { background:none; }
#oferta-programowa-cyfrowe thead th { vertical-align:bottom; border:none; }
#oferta-programowa-cyfrowe .logo { text-align:center; background-color:#fff; border:none; }
#oferta-programowa-cyfrowe .vertical { height:30px; white-space:nowrap; line-height:30px; padding:2px 0; }

#oferta-programowa-cyfrowe .prog-pakiet-24 {background-color:#EBEBEB;} /* Biały */
#oferta-programowa-cyfrowe .prog-pakiet-25 {background-color:#B88019;} /* Brązowy */
#oferta-programowa-cyfrowe .prog-pakiet-26 {background-color:#CACACA;} /* Srebrny */
#oferta-programowa-cyfrowe .prog-pakiet-27 {background-color:#FFE554;} /* Złoty */
#oferta-programowa-cyfrowe .prog-pakiet-28 {background-color:#E7E4E4;} /* Platynowy */
#oferta-programowa-cyfrowe .prog-pakiet-8 {background-color:#94CFFF;} /* CANAL+ */
#oferta-programowa-cyfrowe .prog-pakiet-10 {background-color:#77A3FF;} /* CANAL+ HD */

#oferta-programowa-analogowe {  }

/* print table background colors */
table td, table th { -webkit-print-color-adjust:exact; }
table { page-break-after:auto }
tr    { page-break-inside:avoid; page-break-after:auto; position:relative; }
td    { page-break-inside:avoid; page-break-after:auto; position:relative; }
thead { display:table-header-group }
tfoot { display:table-footer-group }
	</style>
		<?php
	}

	function cyfrowe($programy, $grupyKosztow, $costs, $orderby = '', $view_type = '') {
		$grupyKosztow_count = array();
		foreach ($grupyKosztow as $pakiet) {
			$grupyKosztow_count[$pakiet->ID] = 0;
		}
		$grupyKosztow_count['HD'] = 0;
		foreach ($programy as $program) {
			foreach ($grupyKosztow as $pakiet) {
				if (in_array($pakiet->ID, $program->pakiety)) {
					$grupyKosztow_count[$pakiet->ID] += 1;
				}
			}

			if ($program->TV_SIGAL_DELIVERY == 'DVB-HD') {
				$grupyKosztow_count['HD'] += 1;
			}
		}

		if ($view_type == 'csv') {
			$this->csv_cyfrowe($programy, $grupyKosztow, $costs, $orderby = '');
			return;
		}

		$this->css();
		$sumyGrupKosztow = array();
?>
	<script type="text/javascript">
(function($){
  $.fn.rotateTableCellContent = function(options){
		/*
			Version 1.0
			7/2011
			Written by David Votrubec (davidjs.com) and
			Michal Tehnik (@Mictech) for ST-Software.com

			@from https://github.com/ST-Software/Scripts/tree/master/jquery.rotateTableCellContent
			modified by Piotr Labudda: set -90deg from 90deg
		*/
		var cssClass = ((options) ? options.className : false) || "vertical";
		var cellsToRotate = $('.' + cssClass, this);
		var betterCells = [];
		cellsToRotate.each(function(){
			var cell = $(this)
				, newText = cell.text()
				, height = cell.height()
				, width = cell.width()
				, newDiv = $('<div>', { height: width, width: height })
				, newInnerDiv = $('<div>', { text: newText, 'class': 'rotated' });
			newInnerDiv.css('-webkit-transform-origin', (width / 2) + 'px ' + (width / 2) + 'px');
			newInnerDiv.css('-moz-transform-origin', (width / 2) + 'px ' + (width / 2) + 'px');
			newDiv.append(newInnerDiv);
			betterCells.push(newDiv);
		});

		cellsToRotate.each(function(i){
			$(this).html(betterCells[i]);
		});
	};
})(jQuery);

jQuery(document).ready(function(){
	var n=jQuery('#oferta-programowa-cyfrowe')
	n.rotateTableCellContent();
	n.find('.logo').append(jQuery('<img src="icon/logo.biall-net.jpg">'));
});
	</script>
	<div style="float:right;color:#aaa;"><?php echo date("Y-m-d"); ?></div>
	<h1>Analiza grup kosztów</h1>
	<table cellspacing="0" cellpadding="0" border="0" id="oferta-programowa-cyfrowe" class="oferta-programowa">
	<thead>
		<tr>
			<td colspan="2" class="logo">
			</td>
			<?php foreach ($grupyKosztow as $pakiet) { ?>
				<th class="vertical prog-pakiet-<?php echo $pakiet->ID; ?>"><?php echo $pakiet->WWW_NAZWA; ?></th>
			<?php } ?>
		</tr>
		<tr>
			<td colspan="2" style="text-align:right">
				Nr grupy kosztów
			</td>
			<?php foreach ($grupyKosztow as $pakiet) { ?>
				<td class="c"><?php echo $pakiet->ID; ?></td>
			<?php } ?>
		</tr>
	</thead>
	<tbody class="cyfrowe-prog-count">
		<tr>
			<td colspan="2" class="p2 r">Liczba projektów</td>
			<?php foreach ($grupyKosztow as $pakiet) { ?>
				<td class="c"><?php echo $grupyKosztow_count[$pakiet->ID]; ?></td>
			<?php } ?>
		</tr>
	</tbody>
	<tbody>
	<?php $t = 1; foreach ($programy as $projekt) { ?>
		<tr class="row-<?php echo ($t = 1 - $t); ?>">
			<td class="p2 r nr"><?php echo $projekt->ID; ?></td>
			<td class="p2"><?php echo $projekt->M_DIST_DESC; ?></td>
			<?php foreach ($grupyKosztow as $grupaKosztow) { ?>
				<?php if (!empty($costs[$projekt->ID][$grupaKosztow->ID])) : ?>
					<?php
						$vCost = $costs[$projekt->ID][$grupaKosztow->ID];
						$sumyGrupKosztow[$vCost['TYPE']][$grupaKosztow->ID] += $vCost['COST'];
					?>
					<td class="c prog-pakiet-<?php echo $grupaKosztow->ID; ?>"><?php echo $vCost['COST']; ?>/<?php echo substr($vCost['TYPE'], 0, 1); ?></td>
				<?php else : ?>
					<td></td>
				<?php endif; ?>
			<?php } ?>
		</tr>
	<?php } ?>
	</tbody>
	<tfoot>
	<?php $t = 1; foreach ($sumyGrupKosztow as $typKosztu => $vSumy) { ?>
		<tr class="row-<?php echo ($t = 1 - $t); ?>">
			<td class="p2 r nr" colspan="2"><?php echo $typKosztu; ?></td>
			<?php foreach ($grupyKosztow as $grupaKosztow) { ?>
				<?php if (!empty($vSumy[$grupaKosztow->ID])) : ?>
					<td class="c"><?php echo $vSumy[$grupaKosztow->ID]; ?></td>
				<?php else : ?>
					<td></td>
				<?php endif; ?>
			<?php } ?>
		</tr>
	<?php } ?>
	</tfoot>
	</table>
<?php
	}

	function csv_cyfrowe($projekty, $grupyKosztow, $costs, $orderby = '') {
		echo'<pre style="overflow:auto;border:1px solid red;text-align:left;">';
		foreach ($projekty as $projekt) {
			$csv_line = array();
			$csv_line []= '"' . $projekt->ID . '"';
			$csv_line []= '"' . $projekt->M_DIST_DESC . '"';
			foreach ($grupyKosztow as $pakiet) {
				if (in_array($pakiet->ID, $projekt->pakiety)) {
					$csv_line []= '"1"';
				} else {
					$csv_line []= '"0"';
				}
			}
			echo implode(';', $csv_line) . "\n";
		}
		echo'</pre>';
	}

	function getGrupyKosztow() {
		$grupyKosztow = array();
		$db = DB::getDB();
		$sql = "select gc.`ID`
				, gc.`POKAZ_NA_WWW`
				, gc.`S_OTHER_INFO` as `WWW_NAZWA`
				, gc.`WWW_P_SERVICE`
		--		, if(gc.`WWW_P_SERVICE`='TVC', 1, 2) as order_by_lp
			from `USERS2_OFFERS_COSTS_GROUPS` as gc
			where
				gc.`A_STATUS` in('NORMAL', 'WAITING')
		--		and gc.`POKAZ_NA_WWW`='TAK'
		--	order by order_by_lp
			order by gc.`ID` DESC
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$grupyKosztow[$r->ID] = $r;
		}
		return $grupyKosztow;
	}

	function getProjekty() {
		$db = DB::getDB();
		$projekty = array();
		$sql = "select c.ID as c_ID
				, c.`ID_PROJECT` as c_ID_PROJECT
				, p.`ID`
				, p.`M_DIST_DESC`
				, p.`TV_NAZWA_PROGRAMU`
				, p.`TV_LANGUAGE`
				, p.`TV_SIGAL_DELIVERY`
				, p.`TV_PROGRAM_PROFILE`
				, p.`TV_DVBC_EPG_CHANNEL_NUMBER`
				, p.`M_DIST_INVENTOR`
		--		, CAST(p.`TV_DVBC_EPG_CHANNEL_NUMBER` AS SIGNED) as order_by_lp
				, '' as order_by_lp
				, GROUP_CONCAT(cg.ID) as cost_group_ids
			from `USERS2_OFFERS_COSTS_GROUPS` as cg
				left join `USERS2_OFFERS_COSTS` as c on(c.`ID_COSTS_GROUPS`=cg.`ID`)
				left join `IN7_MK_BAZA_DYSTRYBUCJI` as p on(p.`ID`=c.`ID_PROJECT`
			--		and p.`M_DIST_TYPE`='TV__UMOWA ZAKUPU KANALU'
				)
			where 1=1
		--		and p.`M_DIST_TYPE`='TV__UMOWA ZAKUPU KANALU' 
		--		and c.`S_OFFER_STATUS`='IN_OFFER' 
		--		and cg.`ID_MAIN_COSTS_GROUP`=47 
				and p.`ID` is not NULL 
		--		and cg.`A_STATUS` in('WAITING', 'NORMAL') 
				and c.`A_STATUS` in('WAITING', 'NORMAL') 
				and p.`A_STATUS` in('WAITING', 'NORMAL') 
		--		and p.`TV_SIGAL_DELIVERY` in ('DVB-SD', 'DVB-HD')
		--		and cg.`POKAZ_NA_WWW`='TAK'
			group by p.ID
		--	order by order_by_lp ASC
			order by ID DESC
			limit 10000
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$r->pakiety = explode(',', $r->cost_group_ids);
			$projekty[] = $r;
		}
		return $projekty;
	}

	function getCosts() {
		$db = DB::getDB();
		$costs = array();
		$sql = "select c.ID as c_ID
				, c.`ID_PROJECT` as c_ID_PROJECT
				, c.`ID_COSTS_GROUPS`
				, c.`COST_ACCOUNTED_ON`
				, c.`COST_POWER`
				, p.`ID`
				, p.`M_DIST_DESC`
			from `USERS2_OFFERS_COSTS_GROUPS` as cg
				left join `USERS2_OFFERS_COSTS` as c on(c.`ID_COSTS_GROUPS`=cg.`ID`)
				left join `IN7_MK_BAZA_DYSTRYBUCJI` as p on(p.`ID`=c.`ID_PROJECT`)
			where 1=1
				and p.`ID` is not NULL 
				and c.`A_STATUS` in('WAITING', 'NORMAL') 
				and p.`A_STATUS` in('WAITING', 'NORMAL') 
			order by p.`ID` DESC
			limit 10000
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$costs[$r->ID][$r->ID_COSTS_GROUPS] = array('COST'=>$r->COST_POWER, 'TYPE'=>$r->COST_ACCOUNTED_ON);
		}
		return $costs;
	}

	function getProgramyAnalogowe() {
		$db = DB::getDB();
		$programy = array();
		$sql = "select c.ID as c_ID
				, c.ID_PROJECT as c_ID_PROJECT
				, p.ID, p.TV_NAZWA_PROGRAMU, p.TV_LANGUAGE, p.TV_SIGAL_DELIVERY
				, p.TV_PROGRAM_PROFILE
			--	, p.TV_DVBC_EPG_CHANNEL_NUMBER
				, p.M_DIST_INVENTOR
				, p.TV_ANALOG_FREQUENCY
				, CAST(p.`TV_DVBC_EPG_CHANNEL_NUMBER` AS SIGNED) as order_by_lp
				, GROUP_CONCAT(cg.ID) as cost_group_ids
			from `USERS2_OFFERS_COSTS_GROUPS` as cg
				left join `USERS2_OFFERS_COSTS` as c on(c.`ID_COSTS_GROUPS`=cg.`ID`)
				left join `IN7_MK_BAZA_DYSTRYBUCJI` as p on(p.`ID`=c.`ID_PROJECT` and p.`M_DIST_TYPE`='TV__UMOWA ZAKUPU KANALU')
			where
				p.`M_DIST_TYPE`='TV__UMOWA ZAKUPU KANALU' 
				and c.`S_OFFER_STATUS`='IN_OFFER' 
				and cg.`ID`=2 
				and p.`ID` is not NULL 
				and cg.`A_STATUS` in('WAITING', 'NORMAL') 
				and c.`A_STATUS` in('WAITING', 'NORMAL') 
				and p.`A_STATUS` in('WAITING', 'NORMAL') 
				and p.`TV_SIGAL_DELIVERY` in ('ANALOG')
				-- and cg.`POKAZ_NA_WWW`='TAK'
			group by p.ID
			order by order_by_lp ASC
			limit 1000;
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$r->pakiety = explode(',', $r->cost_group_ids);
			$programy []= $r;
		}
		return $programy;
	}

}