<?php
  include("html_functions.php");



  ################## PAGE INITIALIZATION #########################

  $lgid=$_SESSION['lgid'];

  $lgpath=$_SESSION['lgpath'];

  $date=new Date($_SESSION['lgdate']);

  $lgyear=$_SESSION['lgyear'];
  
  $yrsToShow=4;

  
  ini_set("memory_limit","64M");

        
  include($_SESSION['adminpath']."/dbopen.php");

  ##### Get Report Settings #####

  $team_id=$_GET['team_id'];

  if ($team_id=="") {$team_id=$_SESSION['user_team_id'];}

  $dyear=$_GET['year'];

  if ($dyear=="") {$dyear=$lgyear+1;}
  
  $metric=$_GET['metric'];
  if ($metric=="") {$metric="overview";}



  if (($_SESSION['user_team_id']!=$team_id)&&($view=='admin')) {$view='all_blocks';}

  if ($view!="admin")

  {

	  $view='all_blocks';

  }



  ##### Get Teams In League #####

  $query="SELECT team_id,name,nickname,abbr,league_id,level,parent_team_id FROM teams WHERE";

  $query.=" (league_id=$lgid OR parent_team_id IN (SELECT team_id FROM teams WHERE league_id=$lgid AND allstar_team=0))";

  $query.=" AND allstar_team=0 ORDER BY name,nickname;";

  if ($_GET['showq']==1) {echo $query."<br/>\n";}

  $result=mysqli_query($db,$query);

  $tmList="";
  $fndTm=0;

  while ($row=mysqli_fetch_array($result))
  {
	  $tid=$row['team_id'];
      if ($tid==$team_id) {$fndTm=1;}
	  $teams[$tid]['abbr']=$row['abbr'];
	  $teams[$tid]['league_id']=$row['league_id'];
	  $teams[$tid]['name']=$row['name']." ".$row['nickname'];
	  $teams[$tid]['level']=get_level($row['level']);
	  if ((($team_id!="")&&(($tid==$team_id)||($team_id==$row['parent_team_id']))) || (($team_id=="")&&($lgid==$row['league_id']))) {$tmList.=",$tid";}
  }
  $tmList=trim($tmList,",");

  mysqli_close($db);

  # Title Bar
  $title="Team Financials";

  page_head($title,$_SESSION['lgpath'],"","","https://d3js.org/d3.v5.min.js");

  echo " <div id='topbar'>\n";
  main_titlebar($lgid,$_SESSION['lgpath'],$title);
  nav_toolbar('front_office',"Front Office");
  echo " </div>   <!-- END topbar DIV -->\n";

  echo " <div id='contentpane'>\n";



  ################## START OF PAGE CONTENT #########################

  include($_SESSION['adminpath']."/dbopen.php");



##### Display Filters #####
echo " <div class='textbox'>\n";
echo "  <form method='get' action='teamFinancials.php' style='clear:left;'>\n";
echo "  <table cellspacing=0 cellpadding=2 border=0>\n";
echo "   <tr class='title'><td colspan=5>Filters</td></tr>\n";
echo "   <tr>\n";


## Show Team Filter
echo "    <td><label for='team_id'><b>Team:</b></label></td>\n";
echo "    <td>\n";
echo "     <select name='team_id' id='team_id'>\n";
foreach ($teams as $tid => $val)
 {
     if ($fndTm==0) {$team_id=$tid;$fndTm=1;}
   if ($teams[$tid]['league_id']!=$lgid) {continue;}
   echo "      <option value='$tid'";
   if ($team_id==$tid) {echo " selected";}
   echo ">".$teams[$tid]['name']."</option>\n";
 }
echo "     </select>\n";
echo "    </td>\n";

echo "    <td>\n";
echo "     <select name='metric' id='metric'>\n";
echo "      <option value='overview'>Financial Overview</option>\n";
echo "      <optgroup label='Graphs'>\n";
$finMetrics = Array('attendance'=>'Attendance',
					'budget'=>'Budget',
					'gate_revenue'=>'Gate Revenue',
					'merchandising_revenue'=>'Merchandising Revenue',
					'media_revenue'=>'Media Revenue',
					'playoff_revenue'=>'Playoff Revenue',
					'revenue_sharing'=>'Revenue Sharing',
					'player_expenses'=>'Player Payroll',
					'staff_expenses'=>'Staff Payroll'
					);
foreach ($finMetrics as $key => $val)
 {
   echo "       <option value='$key'";
   if ($metric==$key) {echo " selected";}
   echo ">".$val."</option>\n";
 }
echo "      </optgroup>\n";
echo "     </select>\n";
echo "    </td>\n";


## Close Form
if ($view!="")
 {
   ## Submission button
   echo "    <td><input type='submit' value='Go'></input></td>\n";
 }
echo "   </tr>\n";
echo "  </table>\n";
echo "  </form>\n";
echo " </div>\n";

echo "<div>".print_r($_SESSION)."</div>\n";


switch ($metric)
{
	##### Financial Overview #####
	case 'overview':
		## Get Season Completion Percentage ##
		$query="SELECT played,COUNT(game_id) as g FROM games WHERE league_id=$lgid AND game_type=0 GROUP BY played;";
		if ($_GET['showq']==1) {echo "$query<br/>\n";}
		$result=mysqli_query($db,$query);
		if ($result)
		{
			while ($row=mysqli_fetch_array($result))
			{
				switch ($row['played'])
				{
					case 0: $unPlayGm=$row['g']; break;
					case 1: $playGm=$row['g']; break;
				}
			}
			$seaCompPct=$playGm/($unPlayGm+$playGm);
		}
		
		## Check league state
		$query="SELECT league_state,season_year,lgs.current_date FROM leagues as lgs WHERE league_id=$lgid";
		if ($_GET['showq']==1) {echo "$query<br/>\n";}
		$result=mysqli_query($db,$query);
		if ($result)
		{
			while ($row=mysqli_fetch_array($result))
			{
				$league_state=$row['league_stat'];
				$season_year=$row['season_year'];
				$curDate=$row['current_date'];
			}
		}
		if ($league_state==0)
		{
			$query="SELECT type,start_date FROM league_events WHERE league_id=100 AND type IN (11,12,16);";
				if ($_GET['showq']==1) {echo "$query<br/>\n";}
			$result=mysqli_query($db,$query);
			if ($result)
			{
				while ($row=mysqli_fetch_array($result))
				{
					$eventType=$row['type'];
					switch ($eventType)
					{
						case 11: $stStarts=$row['start_date']; break;   //Spring Training Begins
						case 12: $odStarts=$row['start_date']; break;   //Opening Day
						case 16: $offseasonStarts=$row['start_date']; break;  //Offseason Starts
					}
				}
			}
			$osStartsTS=strtotime($offseasonStarts);
			$osEndsTS=strtotime($odStarts);
			$curDateTS=strtotime($curDate);
			$offseasonLength=($osEndsTS-$osStartsTS)/60/60/24;
			$offseasonComp  =($curDateTS-$osStartsTS)/60/60/24;
			$offseasonCompPct=$offseasonComp/$offseasonLength;
		}
		
		## Get Financial Data ##
		$finFields=Array('budget'=>'Budget',
			'scouting_budget'=>'Scouting',
			'draft_budget'=>'Draft',
			'development_budget'=>'Development',
			'previous_balance'=>'Previous Balance',
			'revenue_sharing'=>'Revenue Sharing',
			'cash_owner'=>'Cash Owner',
			'attendance'=>'Attendance',
			'gate_revenue'=>'Gate Revenue',
			'season_tickets'=>'Season Tickets',
			'season_ticket_revenue'=>'Season Ticket Revenue',
			'playoff_revenue'=>'Playoff Revenue',
			'media_revenue'=>'Media Revenue',
			'merchandising_revenue'=>'Merchandising Revenue',
			'player_expenses'=>'Player Expenses',
			'staff_expenses'=>'Staff Expenses',
			'draft_expenses'=>'Draft Expenses',
			'spent_in_intl'=>'International Amateur FA',
			'cash_trades'=>'Cash Trades',
			'cash'=>'Cash Balance'
			);
		$othFields=Array('market'=>'Market',
			'fan_loyalty'=>'Fan Loyalty',
			'fan_interest'=>'Fan Interest',
			'fan_modifier'=>'Fan Modifier',
			'player_payroll'=>'Player Payroll',
			'player_payroll_offered'=>'Offered Contracts'
			);
		
		## Current year financials
		$query="SELECT * FROM team_financials WHERE team_id=".$team_id;
		if ($_GET['showq']==1) {echo "$query<br/>\n";}
		$result=mysqli_query($db,$query);
		while ($row=mysqli_fetch_array($result))
		{
			$yr=$lgyear;
			
			foreach ($finFields as $field => $fieldName)
			{
				$finances[$yr][$field]=$row[$field];
			}
			foreach ($othFields as $field => $fieldName)
			{
				$tmFinances[$yr][$field]=$row[$field];
			}
		}
		
		## Historical financials
		$query="SELECT * FROM team_history_financials as thf WHERE team_id=".$team_id." AND thf.year>=".($lgyear-$yrsToShow)." ORDER BY year";
		if ($_GET['showq']==1) {echo "$query<br/>\n";}
		$result=mysqli_query($db,$query);
		while ($row=mysqli_fetch_array($result))
		{
			$yr=$row['year'];

			foreach ($finFields as $field => $fieldName)
			{
				$finances[$yr][$field]=$row[$field];
			}
		}
		ksort($finances);
		
		## Display Current Year Information
		echo " <div class='textbox' style='border:0px;'>\n";
		echo "  <p><b>Market Size:</b> <span class='rating_".sprintf("%02d",min(20,2*$tmFinances[$lgyear]['market']))."'>".stat_format('market',$tmFinances[$lgyear]['market'])."</span></p>";
		echo "  <p><b>Fan Loyalty:</b> <span class='rating_".sprintf("%02d",min(20,2*$tmFinances[$lgyear]['fan_loyalty']))."'>".stat_format('fan_loyalty',$tmFinances[$lgyear]['fan_loyalty'])."</span></p>";
		if ($team_id==$_SESSION['user_team_id'])
		{
			$fi = ($tmFinances[$lgyear]['fan_interest']+$tmFinances[$lgyear]['fan_modifier']);
			echo "  <p><b>Fan Interest:</b> <span class='rating_".sprintf("%02d",$fi/5)."'>$fi</span></p>";
		}
		if (($league_state==0)&&($_GET['showq']==1))
		{
			echo "<p>
			Offseason Start: $offseasonStarts<br/>
			Current Date: $curDate<br/>
			Spring Training Starts: $stStarts<br/>
			Opening Day: $odStarts<br/>
			Offseason Length: $offseasonLength<br/>
			Offseason Comp: $offseasonComp<br/>
			Offseason %: $offseasonCompPct<br/>
			</p>\n";
		}
		echo " </div>\n";
		
		## Display Historical Data
		echo " <div class='textbox'>\n";
		echo "  <table cellpadding=2 cellspacing=0 border=0 width='850px'>\n";
		echo "   <thead>\n";
		echo "    <tr class='title2'><td colspan=".($yrsToShow+3).">".$teams[$team_id]['name']." Finances</td></tr>\n";
		echo "    <tr class='headline'>";
		echo "<td>&nbsp;</td>";
		foreach ($finances as $yr => $val)
		{
			echo "<td class='hsc2'>$yr";
			if ($yr==$lgyear) {echo " YTD";}
			echo "</td>";
			if ($yr==$lgyear) {echo "<td class='hsc2'>$yr Proj.</td>";}
		}
		echo "</tr>\n";
		echo "   </thead>\n";
		$prevYr=0;
		$rowCnt=0;
		foreach ($finFields as $field => $fieldName)
		{
			if (strpos($field,"_budget"))
			{
				if ($team_id!=$_SESSION['user_team_id']) {continue;}
			}
			
			if ($field=="budget") {echo "<tr class='title'><td colspan=".($yrsToShow+3).">Budgets</td></tr>\n";}
			if ($field=="previous_balance") {echo "<tr class='title'><td colspan=".($yrsToShow+3).">Starting Cash</td></tr>\n";}
			if ($field=="attendance")
			{
				echo "<tr class='title'><td colspan=".($yrsToShow+3).">Revenue</td></tr>\n";
				$subTotCat="tot_revenue";
			}
			if ($field=="player_expenses")
			{
				echo "<tr class='headline'><td class='hsc2'>Revenue Subtotal<sup>*</sup>:</td>";
				foreach ($finances as $yr => $val) {echo "<td class='hsc2'>".stat_format($subTotCat,$finances[$yr][$subTotCat])."</td>";}
				echo "<td class='hsc2'>".stat_format($subTotCat,$projFinances[$yr][$subTotCat])."</td>";
				echo "</tr>\n";
				echo "<tr class='title'><td colspan=".($yrsToShow+3).">Expenses</td></tr>\n";
				$subTotCat="tot_expenses";
			}
			if ($field=="cash_trades")
			{
				echo "<tr class='headline'><td class='hsc2'>Expenses Subtotal<sup>*</sup>:</td>";
				foreach ($finances as $yr => $val) {echo "<td class='hsc2'>".stat_format($subTotCat,$finances[$yr][$subTotCat])."</td>";}
				echo "<td class='hsc2'>".stat_format($subTotCat,$projFinances[$yr][$subTotCat])."</td>";
				echo "</tr>\n";
				echo "<tr class='title'><td colspan=".($yrsToShow+3).">Cash This Year</td></tr>\n";
				$subTotCat="net_cash";
			}
			
			$cls='s'.($rowCnt%2+1);
			echo "   <tr class='".$cls."'>";
			echo "<td class='".$cls."_l'>".$fieldName."</td>";
			foreach ($finances as $yr => $val)
			{
				echo "<td>".stat_format($field,$finances[$yr][$field])."</td>";
				if (($field!='attendance')&&($field!='season_tickets')) {$finances[$yr][$subTotCat]=$finances[$yr][$subTotCat]+$finances[$yr][$field];}
				
				// Add current year projection
				if ($yr==$lgyear)
				{
					echo "<td>";
					switch ($field)
					{
						case 'player_expenses':
							if ($seaCompPct==0) {$projVal=$tmFinances[$lgyear]['player_payroll'];}
							else {$projVal=$finances[$yr][$field]+((1-$seaCompPct)*$tmFinances[$lgyear]['player_payroll']);}
							echo stat_format($field,$projVal);
							break;
						case 'attendance':
							if ($seaCompPct==0) {$projVal=$finances[$prevYr][$field];}
							else {$projVal=$finances[$yr][$field]/$seaCompPct;}
							echo stat_format($field,$projVal);
							break;
						case 'gate_revenue':
						case 'merchandising_revenue':
						case 'staff_expenses':
							if ($seaCompPct==0) {$projVal=$finances[$prevYr][$field];}
							else {$projVal=$finances[$yr][$field]/$seaCompPct;}
							echo stat_format($field,$projVal);
							break;
						case 'season_tickets':
						case 'season_ticket_revenue':
							if ($seaCompPct>0) {$projVal=$finances[$yr][$field];}
							else {$projVal=$finances[$yr][$field]/$offseasonCompPct;}
							echo stat_format($field,$projVal);
							break;						
						default:
							$projVal=$finances[$yr][$field];
							echo stat_format($field,$projVal);
							break;
					}
					echo "</td>";

					if (($field!='attendance')&&($field!='season_tickets')) {$projFinances[$yr][$subTotCat]=$projFinances[$yr][$subTotCat]+$projVal;}
				}
			
				$prevYr=$yr;
			}
			echo "</tr>\n";
			$rowCnt++;
		}
		echo "  </table>\n";
		echo " </div>";
		
		echo "<div class='textbox' style='border:0px;'><p>* Not all revenue or expense fields reported</p></div>\n";
		break;

	##### Financial Charts #####
	default:
		##### Create Container #####
		echo " <div id='finchart' class='textbox'></div>\n";

		##### Get Data #####
		$query="SELECT year,$metric as tmValue FROM team_history_financials as thf WHERE team_id=".$team_id." AND thf.year>=".($lgyear-25)." ORDER BY year";
		$query="SELECT year,$metric as tmValue FROM team_history_financials as thf WHERE team_id=".$team_id." ORDER BY year";
		if ($_GET['showq']==1) {echo "$query<br/>\n";}
		$result=mysqli_query($db,$query);
		$i=0;
		  while ($row=mysqli_fetch_array($result))
		  {
			  $yr=$row['year'];
			  $val=$row['tmValue'];
			  $finData[$i]['year']=$yr;
			  $finData[$i]['tmValue']=$val;
			  
			  $i++;
		  }
		  
		break;
}


  mysqli_close($db);

  ################## END OF PAGE #########################

  echo " </div>   <!-- END contentpane DIV -->\n";

  page_foot($_SESSION['lgpath']);
  
if ($metric!="overview") {
  echo "<script>\n";
  echo "document.addEventListener(\"DOMContentLoaded\", function(e) {
	  var finData = ".json_encode($finData).";	  
	  var width= 800;
	  var height=400;
	  var margin = {top: 20, right: 20, bottom: 40, left: 80};
			
	  finData.forEach(function(d) {
            d.year = parseInt(d.year);
            d.tmValue = parseInt(d.tmValue);
        });

	  var xMin = d3.min([0, d3.min(finData, function(d) { return d.year;})]);
	  var xMax = d3.max(finData, function (d) {return d.year;});
	  //var xScale = d3.scaleBand().rangeRound([0, width]).padding(.05);
	  var xScale = d3.scaleBand()
		.domain([xMin, xMax])
		//.domain([d3.min(finData), d3.max(finData)])
		.range([0, width])
		.padding(.05);

	  var yMin = d3.min([0, d3.min(finData, function(d) { return d.tmValue;})]);
	  var yMax = d3.max(finData, function (d) {return d.tmValue;});
	  var yScale = d3.scaleLinear()
		.domain([yMin, yMax])
		//.domain([d3.min(finData), d3.max(finData)])
		.range([height, 0]);
	  var yZero = yScale(0);


	  var xAxis = d3.axisBottom()
		.scale(xScale)
		.tickFormat(function(d) { return (((d%";
  if ($i<25) {echo "1";}
	elseif ($i<50) {echo "2";}
	else {echo 5;}
  echo ")==0)?d:null); })
		;

	  var yAxis = d3.axisLeft()
		.scale(yScale)
		.ticks(10)
		.tickFormat(d3.format('".($metric=="attendance"?"":"$").",'))
		;
		
	  function make_y_gridlines() {
		 return d3.axisLeft(yScale)
			.ticks(10);
	  }
	  	  	 
	  var tooltipDiv = d3.select('body').append('div')
		.attr('class', 'chart-tooltip')
		.style('opacity', 0);
	 
	  var svgContainer = d3.select('#finchart').append(\"svg\")
		.attr('width', width + margin.left + margin.right)
		.attr('height', height + margin.top + margin.bottom)
		.append('g')
		.attr('transform', 
			'translate(' + margin.left + ',' + margin.top + ')');
		
	  xScale.domain(finData.map(function(d) { return d.year; }));
	  	  
	  svgContainer.append('g')
			.attr('class', 'x axis')
			.attr('transform', 'translate(0,' + height + ')')
			.call(xAxis)
		.selectAll('text')
			.style('text-anchor', 'end')
			.attr('dx', '-.8em')
			.attr('dy', '-.55em')
			.attr('transform', 'rotate(-90)' );
	
	  // Add Axes
	  svgContainer.append('g')
			.attr('class', 'y axis')
			.call(yAxis)
		.append('text')
			.attr('transform', 'rotate(-90)')
			.attr('y', 6)
			.attr('dy', '.71em')
			.style('text-anchor', 'end')
			.text('Value ($)');
	  
	  // Add Y Gridlines
	  var yGrid = svgContainer.append('g')
		.attr('class', 'gridLines')
		.call(make_y_gridlines()
			.tickSize(-width)
			.tickFormat('')
			)
		.call(g => g.select('.domain')
			.remove()
			)
		.call(g => g.selectAll('.tick line')
			.attr('stroke-opacity', 0.5)
			.attr('stroke-dasharray', '2,3')
			)
		;
	
	  // Add columns/bars
	  svgContainer.selectAll('bar')
			.data(finData)
		.enter().append('rect')
			.style('fill', (yMin<0 ? d => (d.tmValue >0 ? 'olivedrab' : 'tomato') : 'steelblue'))
			.attr('x', function(d) { return xScale(d.year); })
			.attr('width', xScale.bandwidth())
			.attr('y', function(d) { return d.tmValue>0 ? yScale(d.tmValue) : yZero; })
			.attr('height', function(d) {return Math.abs(yScale(d.tmValue)-yZero);})
			.attr('title', function(d) {return d.year + ', ' + d3.format(',')(d.tmValue);})
			.on('mouseover', function (d, i) {
				d3.select(this).transition()
					.duration('50')
					.attr('opacity', '.85');
				tooltipDiv.transition()
					.duration(50)
					.style('opacity', 1);
				tooltipDiv.html(d3.format('".($metric=="attendance"?"":"$").",')(d.tmValue))
					.style('left', (d3.event.pageX + 10) + 'px')
					.style('top', (d3.event.pageY - 15) + 'px');
				}
				)
			.on('mouseout', function (d, i) {
				d3.select(this).transition()
					.duration('50')
					.attr('opacity', '1');
				tooltipDiv.transition()
					.duration('50')
					.style('opacity', 0);
				}
				);
	  
	});";
  echo "</script>\n";
}

?>

