<?php

session_start();
if($HTTP_SESSION_VARS['dbName']==""){
header("location:../index.php");
}

print <<< HERE
<HTML>
<HEAD>
<TITLE> Statistics Report </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">

<style type="text/css">
table.params td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }

			 
table.master th { font-size: 14px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
				 }
				 
table.master td { font-size: 13px; 
                  font-family:Times-New-Roman;
                 }


</style>
</HEAD>

HERE;
include("config.inc.php");


//Set Time Execution For the Report
ini_set('max_execution_time', $config['ReportTimeExecution']);

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass']) or die("Could not connect to DB");
mysql_select_db($HTTP_SESSION_VARS['dbName'],$connection) or die("Could not find DB");


$querylog="SELECT print as printaccess from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' 	and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."')	";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}


if ($printaccess==0 or $printaccess=='')
echo'<BODY oncontextmenu="return false;">';
else
echo'<BODY>';

$querysetup="select DecimalBase, DecimalBase1, DecimalBase2, Base1, Base2, Company_Name As CompanyName from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
}



$now=current_server_time($connection);
$dt=split(" ",$now[0]);
$printdate=split("-",$dt[0]);


$SelectedOptions = "Filter by: <br>";
if ($fromdate != '')     $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."<br>";	
if ($curr != '')  		 $SelectedOptions .= "-> Currency: ". $curr ."<br>";	

if (!empty($fromdate)) $fromdate = explodeword($fromdate);
if (!empty($todate))   $todate = explodeword($todate);   

$vMonthFrom = intval(substr($fromdate, 5, 2));
$vMonthTo   = intval(substr($todate, 5, 2));
$vYearFrom  = substr($fromdate, 0, 4);
$vYearTo    = substr($todate, 0, 4);     

$curr      	 = GetSQLValueString($curr, 'text');
$fromdate  	 = GetSQLValueString($fromdate, 'date');
$todate      = GetSQLValueString($todate, 'date'); 

if (empty($prtrecords)) $prtrecords = 0;
    
print <<< HERE
<table width="100%" border="0" align="left" >
<tr><td>
<table width="100%" border="0" align="center">
<tr>
  <td width='30%'><b>$CompanyName</b></td>
  <td width='50%' align='center'><font size=4><b>Statistics Report</b></font></td>
  <td width='10%'><font size=1><b> Printed Date: </b> <br><b>Time: </b>
  <td width='10%'><font size=1>
HERE;

echo $printdate[2]."/".$printdate[1]."/".$printdate[0];
echo "<br>".$dt[1]." ".$dt[2];

 print <<<HERE
</font></td>
</tr>
<tr>
   <td colspan='4' width='100%'> <hr width='100%' color='black' size='1'> </td>
</tr>
</table>


<table class="params" width="100%" border="0" align="center" >
<tr>
  <td width="100%" valign='top'><b>$SelectedOptions</b></td>
</tr>
<tr>
   <td width='100%'> <hr width='100%' color='black' size='1'> </td>
</tr>
</table>

HERE;

	

$query = '';

$query = $query." Select JournalType, ".
				" if(JournalType='PV', 'Payment', if(JournalType='RV', 'Receipt', if(JournalType='DV', 'Debit Note', if(JournalType='CV', 'Credit Note', ".
				" if(JournalType='SI', 'Sales', if(JournalType='SR', 'Return Sales', if(JournalType='JS', 'Sales JV', if(JournalType='JP', 'Purchase JV', ".
				" if(JournalType='PI', 'Purchase', if(JournalType='PR', 'Return Purchase', '')))))))))) As Description, ".
				" Sum(TotalAmount) AS TotalAmount, Count(JournalRef) AS TotalRecords ";


for ( $y = $vYearFrom; $y <= $vYearTo; $y += 1) {

	if ($y == $vYearFrom && $y == $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = $vMonthTo; }
	else if ($y == $vYearFrom && $y != $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = 12; }
	else if ($y == $vYearTo && $y != $vYearFrom) { $vMFrom = 1; $vMTo = $vMonthTo; }
	else { $vMFrom = 1; $vMTo = 12; }
				
	for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
					
		$query = $query.
				", Sum(if((concat(month(JournalDate), year(JournalDate)) = '".$m.$y."'), TotalAmount, 0)) AS ".getMonth($m).$y."Amount ".
				", Count(if((concat(month(JournalDate), year(JournalDate)) = '".$m.$y."'), JournalRef, Null)) AS ".getMonth($m).$y."Records ";
					
	}

}
$query = $query." From ( ";
$query = $query." Select Journal.Journal_ID As JournalID, Journal_RefGovernment As JournalRef, Journal_Type As JournalType, Journal.Journal_date As JournalDate, ".
				" if($curr = '".$Base1."', ifNull(Amnt1_db,0), ifNull(Amnt2_db,0)) AS TotalAmount ".
				" From Journal ".
				" Where Date_Format(Journal.Journal_date,'%Y-%m-%d') between ifNull(". $fromdate.", Date_Format(Journal.Journal_date,'%Y-%m-%d')) and ifNull(". $todate.", Date_Format(Journal.Journal_date,'%Y-%m-%d')) ".
				" And Journal_Type In ( ";

				if ($TransPV == 1) $query = $query ."'PV', ";
				if ($TransRV == 1) $query = $query ."'RV', ";
				if ($TransDV == 1) $query = $query ."'DV', ";
				if ($TransCV == 1) $query = $query ."'CV', ";
				if ($TransJS == 1) $query = $query ."'JS', ";
				if ($TransJP == 1) $query = $query ."'JP', ";

				$query = $query ."'') ";
				
if ($TransSI == 1 || $TransSR == 1) {
	$query = $query." Union ";
	$query = $query." Select Journal.Journal_ID As JournalID, Journal_RefGovernment As JournalRef, Journal_Type As JournalType, Journal.Journal_date As JournalDate, ".
					" if($curr = '".$Base1."', ifNull(Journaldt_amount1,0), ifNull(Journaldt_amount2,0)) AS TotalAmount ".
					" From Journal ".
					" Inner Join JournalDt on JournalDt.Journal_ID = Journal.Journal_ID ".
					" Inner Join Clients on JournalDt.Ledger_Number = Clients.Ledger_Number ".
					" Where Date_Format(Journal.Journal_date,'%Y-%m-%d') between ifNull(". $fromdate.", Date_Format(Journal.Journal_date,'%Y-%m-%d')) and ifNull(". $todate.", Date_Format(Journal.Journal_date,'%Y-%m-%d')) ".
					" And Journal_Type In ( ";

				if ($TransSI == 1) $query = $query ."'SI', ";
				if ($TransSR == 1) $query = $query ."'SR', ";

				$query = $query ."'') ";
}

if ($TransPI == 1 || $TransPR == 1) {
	$query = $query." Union ";
	$query = $query." Select Journal.Journal_ID As JournalID, Journal_RefGovernment As JournalRef, Journal_Type As JournalType, Journal.Journal_date As JournalDate, ".
					" if($curr = '".$Base1."', ifNull(Journaldt_amount1,0), ifNull(Journaldt_amount2,0)) AS TotalAmount ".
					" From Journal ".
					" Inner Join JournalDt on JournalDt.Journal_ID = Journal.Journal_ID ".
					" Inner Join Suppliers on JournalDt.Ledger_Number = Suppliers.Ledger_Number ".
					" Where Date_Format(Journal.Journal_date,'%Y-%m-%d') between ifNull(". $fromdate.", Date_Format(Journal.Journal_date,'%Y-%m-%d')) and ifNull(". $todate.", Date_Format(Journal.Journal_date,'%Y-%m-%d')) ".
					" And Journal_Type In ( ";

				if ($TransPI == 1) $query = $query ."'PI', ";
				if ($TransPR == 1) $query = $query ."'PR', ";

				$query = $query ."'') ";
}

$query = $query." ) Q1 ";
$query = $query."  Group By JournalType ".
				"  Order By JournalType ";

//echo $query;exit;

 
echo "<table  border='0' width='100%' align='center' class='master' style='table-layout:fixed; border-collapse:collapse;' cellpadding='0' cellspacing='0'>";
echo"	<col width=150>";
		
for ( $y = $vYearFrom; $y <= $vYearTo; $y += 1) {

	if ($y == $vYearFrom && $y == $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = $vMonthTo; }
	else if ($y == $vYearFrom && $y != $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = 12; }
	else if ($y == $vYearTo && $y != $vYearFrom) { $vMFrom = 1; $vMTo = $vMonthTo; }
	else { $vMFrom = 1; $vMTo = 12; }
				
	for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
		echo" <col width=120>";
		
		if($prtrecords == 1) echo" <col width=80>";
	}
}		

echo"	<col width=130>";

if($prtrecords == 1)
	echo"	<col width=100>";

echo"<tr height='25'>
		<th align='center'>&nbsp;</th>";
		
for ( $y = $vYearFrom; $y <= $vYearTo; $y += 1) {

	if ($y == $vYearFrom && $y == $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = $vMonthTo; }
	else if ($y == $vYearFrom && $y != $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = 12; }
	else if ($y == $vYearTo && $y != $vYearFrom) { $vMFrom = 1; $vMTo = $vMonthTo; }
	else { $vMFrom = 1; $vMTo = 12; }
			
	$CountMonths = 0;		
	for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
		$CountMonths += 1;
		
		if($prtrecords == 1) $CountMonths += 1;
	}
	
	echo "<th align='center' colspan='$CountMonths'>".$y."</th>";
}		

echo"	<th align='center'>&nbsp;</th>";	

if($prtrecords == 1)
	echo"	<th align='center'>&nbsp;</th>";	
	
echo"</tr>";

echo"<tr>
		<th align='center'>Movement</th>";
		
for ( $y = $vYearFrom; $y <= $vYearTo; $y += 1) {

	if ($y == $vYearFrom && $y == $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = $vMonthTo; }
	else if ($y == $vYearFrom && $y != $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = 12; }
	else if ($y == $vYearTo && $y != $vYearFrom) { $vMFrom = 1; $vMTo = $vMonthTo; }
	else { $vMFrom = 1; $vMTo = 12; }
				
	for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
		echo "<th align='center'>".getMonth($m)."</th>";
		
		if($prtrecords == 1) echo "<th align='center'>Records</th>";
	}
}		

echo"	<th align='center'>Total Period</th>";	

if($prtrecords == 1)
	echo"	<th align='center'>Total Records</th>";	
	
echo"</tr>";
	
echo"<tr><td colspan='18'><hr width='100%' color='black' size='2'></td></tr>";
 


$result=mysql_query($query,$connection) or die(mysql_error());
while($rowsjournal=mysql_fetch_row($result)){

	
	
	echo "<tr>";
	echo "	<th align='left'   valign='top'>". $rowsjournal[1] ."</th>";
	
	$i = 4; 
	for ( $y = $vYearFrom; $y <= $vYearTo; $y += 1) {

		if ($y == $vYearFrom && $y == $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = $vMonthTo; }
		else if ($y == $vYearFrom && $y != $vYearTo) { $vMFrom = $vMonthFrom; $vMTo = 12; }
		else if ($y == $vYearTo && $y != $vYearFrom) { $vMFrom = 1; $vMTo = $vMonthTo; }
		else { $vMFrom = 1; $vMTo = 12; }
					
		for ( $m = $vMFrom; $m <= $vMTo; $m += 1) {
			echo "<td align='right'  valign='top'>". format_currency($rowsjournal[$i], $DecimalBase) ."</td>";
			
			if($prtrecords == 1)
				echo "<td align='right'  valign='top'>". $rowsjournal[$i + 1] ."</td>";
				
			$i += 2; 
		}
	}
	
	echo "	<td align='right' valign='top' style='font-weight:bold;'>". format_currency($rowsjournal[2], $DecimalBase) ."</td>";
	
	if($prtrecords == 1)
		echo "	<td align='right' valign='top' style='font-weight:bold;'>". $rowsjournal[3] ."</td>";
		
	echo"</tr>";
	
		
}


echo "</table>";	
	

function current_server_time($connection){

  $string="Select date_format(NOW(),'%Y-%m-%d %h:%i %p') ";
  $result=mysql_query($string,$connection) or die(mysql_error());
  $now=mysql_fetch_row($result);

  return $now;
}

function explodeword($date)
{

$trim = explode('/',$date);
$trim = "$trim[2]"."-"."$trim[1]"."-"."$trim[0]";
return $trim;
}

function getMonth($month){
     return date('M', mktime(0,0,0,$month,1));
}

function format_currency($amount, $DecimalBase) { 

    if($amount < 0) 
        return "(".number_format(abs($amount),$DecimalBase).")"; 
 
    else return number_format($amount, $DecimalBase); 
} 

function GetSQLValueString($theValue, $theType) 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "Null";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "Null";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;
  }
  return $theValue;
}


if ($printaccess==1)
print <<< HERE
<script language=javascript>
if (typeof(window.print) != 'undefined')
{
    window.print();
}
</script>
</BODY>
</HTML>
HERE;

?>