<?php 
//*********************GET RECORDS****************START HERE********************25-10-2012*********AMIT SINGH***********

$SQL_Comp=DB_query("Select * from companies where CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
$Company_Name=DB_fetch_array($SQL_Comp);// GET COMPANY NAME


$SQL_Loc=DB_query("Select * from locations where CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
$Loc_Name=DB_fetch_array($SQL_Loc);// GET BRANCH PREFIX


$FinY=DB_query("SELECT *,YEAR(BookPeriodStart) AS SYear,YEAR(BookPeriodEnd) AS EYEAR FROM companies where CompanyId='".$_SESSION['ACGCompanyId']."'",$db); 
$Finacial=DB_fetch_array($FinY);// FINANCIAL YEAR

//*********************GET RECORDS****************START HERE********************25-10-2012*********AMIT SINGH***********
?>
<link rel="stylesheet" href="css/new_mxerp.css">
<div class="body_contain">
<div class="tab_div">
<h1>Sale Analysis Report</h1>
	
<!---------------------------SEARCHING---------------START HERE------------------------------------------------------->	
	
<div class="centre" id="slidingDiv">
<form method="post">
<table width="100%" cellpadding="3" cellspacing="0">
<tr class="row1">
	<td class="tabledata1">Finacial Year</td>
	<td>
		<?php 
$FinSQL=DB_query("SELECT *,YEAR(BookPeriodStart) AS SYear,YEAR(BookPeriodEnd) AS EYEAR FROM companies where CompanyId='".$_SESSION['ACGCompanyId']."'",$db);?>
		<select name="fin_year" id="fin_year">
		`		<option value="">Select Financial Year</option>
		<?php while($GetFinYear=DB_fetch_array($FinSQL)) {?>
				<option value="<?php echo $GetFinYear['SYear']."-".$GetFinYear['EYEAR']; ?>"<?php echo($_REQUEST['fin_year']==$GetFinYear['SYear']."-".$GetFinYear['EYEAR']?'selected':''); ?>><?php echo $GetFinYear['SYear']."-".$GetFinYear['EYEAR']; ?></option>
		<?php } ?>
			</select>
		</td>
		<td class="tabledata1">Client</td>
		<td><select style="width:150px;"  name="client">
		<option value="All">All</option>
		<?php
		  $ClientSQL="Select * from clientmaster where CompanyId='".$_SESSION['ACGCompanyId']."' and  Site in('".$_SESSION['AssignedBranch']."') 
		and Zone in(".$_SESSION['AssignedSites'].") order by c_name";
		$ClientRes=DB_query($ClientSQL,$db);
		while($ClientRow=DB_fetch_array($ClientRes))
		{
 		?>
		<option value="<?php echo $ClientRow['id']?>" <?php echo ($_REQUEST['client']==$ClientRow['id']?'selected':'');?>><?php echo $ClientRow['c_name']?></option>
		<?php  } ?>
 	</select></td></td>
		</tr>
   <tr class="row1">
   <td colspan="4"><p align="center"><input type="submit" name="Search" VALUE="Search" class="search"></p></td>
  </tr>
</table>
</form>
</div>
<?php  
if($_REQUEST['Search'])
{
	if($_REQUEST['fin_year']!='')
	{
		$filter[]="fin_year='".$_REQUEST['fin_year']."'";
	}
	if($_REQUEST['client']!='All')
	{
		$filter[]="client_id='".$_REQUEST['client']."'";
	}
	if($filter!='')
	{
		$filter1=implode(" and ",$filter);
	 	$SQL="SELECT * from sale_budget where $filter1 group by client_id";// QUERY WITH FILTERED DATA
	}
	else
	{
		$SQL="SELECT * FROM sale_budget GROUP BY client_id  ";// QUERY WITHOUT FILTERED DATA
	}
?>	


<!---------------------------SEARCHING---------------END HERE------------------------------------------------------->
	
<?php 
$SQLClient=DB_query($SQL,$db);
if(DB_num_rows($SQLClient)>0){
?>	
 <p style="text-align:right;"><a  href="#"  onclick='popup("Sale_analysis_report-Print.php?fin_year=<?php echo $_REQUEST['fin_year'];?>&client=<?php echo $_REQUEST['client'];?>",1000,600)' >Print</a></p>
<table width="100%" cellpadding="3" cellspacing="0" border="0">
<tr class="row1">
	<td class="tabledata1"><center><span style="font-size:18px">
<?php echo $Company_Name['CompanyName']; ?> - <?php echo $Loc_Name['prefix'];?> (<?php echo $Finacial['SYear']."-".$Finacial['EYEAR'] ?>)</span><br />
		Sale Report [Super Analysis Report] <!--From 01/04/2012 to 18/10/2012--></center>
	</td>
</tr>
</table>
<div style="width:956px; overflow:auto;">
<table width="100%" border="1" cellspacing="0" cellpadding="3">
<?php 
$Month=array("Jan","Feb","March","Apr","May","June","July","Aug","Sept","Oct","Nov","Dec","Jan","Feb","March");//MONTH IN ARRAY 
while($GetClient=DB_fetch_array($SQLClient)){?>
<tr class="row1">
	<td class="tabledata" style="white-space:nowrap;"><strong><?php echo $s->GetFieldValue('clientmaster',"id='".$GetClient['client_id']."'",'c_name'); ?></strong></td>
<?php for($i=3;$i<count($Month);$i++) {
$j=$i+1;
if($i>=12) {
$j=$i-12;
$j=$j+1;
} 
$FinY1=DB_query("SELECT *,YEAR(BookPeriodStart) AS SYear,YEAR(BookPeriodEnd) AS EYEAR FROM companies where CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
$FinYRow=DB_fetch_array($FinY1);// GET FINACIAL YEAR
if($j>3 && $j<=12)
	  $finyear=$FinYRow['SYear'];
	  else
	  $finyear=$FinYRow['EYEAR'];
	?>
    <td colspan="3" class="tabledata"><center><strong><?php echo $Month[$i]." ".$finyear; ?></strong></center></td>
	<?php } ?>
  </tr>
<tr class="row1">
  <td class="tabledata" style="white-space:nowrap">Shade Name</td>
<?php   for($i=3;$i<count($Month);$i++) { ?>
	<td class="tabledata" style="white-space:nowrap;">Expected Qty</td>
    <td class="tabledata" style="white-space:nowrap;">Sale Qty</td>
    <td class="tabledata">Amount</td>
<?php } ?>
  </tr>
<?php $AsignItem=$s->GetFieldValue('clientmaster',"id='".$GetClient['client_id']."'",'assigned_items'); //GET ASIGN ITEMS
  		$Exp=explode(",",$AsignItem);
		for($k=0;$k<count($Exp);$k++){?>
<tr class="row1">
    <td><?php echo $s->GetFieldValue('item_master',"id='".$Exp[$k]."'",'item_name'); ?></td>
<?php
for($i=3;$i<count($Month);$i++) { 
$j=$i+1;
if($i>=12) {
$j=$i-12;
$j=$j+1;
} 
$FinY1=DB_query("SELECT *,YEAR(BookPeriodStart) AS SYear,YEAR(BookPeriodEnd) AS EYEAR FROM companies where CompanyId='".$_SESSION['ACGCompanyId']."'",$db);

$FinYRow=DB_fetch_array($FinY1);// GET FINACIAL YEAR
if($j>3 && $j<=12)
$finyear=$FinYRow['SYear'];
else
$finyear=$FinYRow['EYEAR'];
$item=array();
$ItemQty=DB_query("SELECT * FROM sale_budget 
INNER JOIN sale_budget_details 
ON (sale_budget.id=sale_budget_details.parant_id)
WHERE sale_budget.month='".$j."' and sale_budget.year='".$finyear."' and sale_budget.client_id='".$GetClient['client_id']."'",$db);
while($abc=DB_fetch_array($ItemQty)){
$item[]= $abc['expected_order'];}
$SQLDis=DB_query("SELECT dispatch.*,dispatch_details.* FROM dispatch 
INNER JOIN dispatch_details 
ON (dispatch.id=dispatch_details.parentId)
WHERE dispatch.party='".$GetClient['client_id']."' and item='".$Exp[$k]."'
AND YEAR(dispatch.dispatchDate)='".$finyear."' 
AND MONTH(dispatch.dispatchDate)='".$j."'",$db);
$Asign_sale_qty=DB_fetch_array($SQLDis);
?>
	<td><?php echo $item[$k]; ?></td>
    <td><?php echo $Asign_sale_qty['dispatchQty']; ?></td>
    <td><?php echo $Asign_sale_qty['AssValue']; ?></td>
<?php } ?>
  </tr>
<?php 
} 

//************ITEM LIST THAT ARE NOT ASIGN TO CLIENT**************START HERE**************************************************

$DispatchSQL=DB_query("select * from dispatch inner join dispatch_details on (dispatch.id=dispatch_details.parentId) 
where dispatch.party='".$GetClient['client_id']."' and item not in ($AsignItem)",$db);
while($Disp_Items=DB_fetch_array($DispatchSQL)){?>
<tr class="row1">
<td><?php echo $s->GetFieldValue('item_master',"id='".$Disp_Items['item']."'",'item_name'); ?></td>
<?php   for($i=3;$i<count($Month);$i++) { 
$j=$i+1;
if($i>=12) {
$j=$i-12;
$j=$j+1;
} 
$FinY1=DB_query("SELECT *,YEAR(BookPeriodStart) AS SYear,YEAR(BookPeriodEnd) AS EYEAR FROM companies where CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
$FinYRow=DB_fetch_array($FinY1);// GET FINACIAL YEAR
if($j>3 && $j<=12)
$finyear=$FinYRow['SYear'];
else
$finyear=$FinYRow['EYEAR'];
$SaleQty=DB_query("SELECT dispatch.*,dispatch_details.* FROM dispatch 
INNER JOIN dispatch_details 
ON (dispatch.id=dispatch_details.parentId)
WHERE dispatch.party='".$GetClient['client_id']."' and item='".$Disp_Items['item']."'
AND YEAR(dispatch.dispatchDate)='".$finyear."' 
AND MONTH(dispatch.dispatchDate)='".$j."'",$db);
$SaleQty=DB_fetch_array($SaleQty);
//print_r($item_arr);
?>
	<td>&nbsp;</td>
    <td><?php echo $SaleQty['dispatchQty']; ?></td>
    <td><?php echo $SaleQty['AssValue']; ?></td>
<?php } ?>
</tr>
<?php }

//************ITEM LIST THAT ARE NOT ASIGN TO CLIENT**************END HERE*****************************************************





//*****************TOTAL AMOUNT ROW***************************************START HERE*******************************************
?>
<tr style="font-weight:bold">
	<td class="tabledata">Total</td>
<?php for($i=3;$i<count($Month);$i++) { 
$j=$i+1;
if($i>=12) {
$j=$i-12;
$j=$j+1;
} 
$FinY1=DB_query("SELECT *,YEAR(BookPeriodStart) AS SYear,YEAR(BookPeriodEnd) AS EYEAR FROM companies where CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
$FinYRow=DB_fetch_array($FinY1);// GET FINACIAL YEAR
if($j>3 && $j<=12)
$finyear=$FinYRow['SYear'];
else
$finyear=$FinYRow['EYEAR'];
$TotalSQL=DB_query("SELECT SUM(sale_budget_details.expected_order) AS TOTALEXP FROM sale_budget 
INNER JOIN sale_budget_details 
ON (sale_budget.id=sale_budget_details.parant_id) 
WHERE sale_budget.month='".$j."' 
AND sale_budget.year='".$finyear."' and sale_budget.client_id='".$GetClient['client_id']."'",$db);
$TotalEx=DB_fetch_array($TotalSQL);


$TotalSaleQty=DB_query("SELECT SUM(dispatch_details.dispatchQty) as TotalDispatch ,SUM(dispatch_details.AssValue) TotalAssValue,dispatch.*,dispatch_details.* FROM dispatch 
INNER JOIN dispatch_details 
ON (dispatch.id=dispatch_details.parentId)
WHERE dispatch.party='".$GetClient['client_id']."'
AND YEAR(dispatch.dispatchDate)='".$finyear."' 
AND MONTH(dispatch.dispatchDate)='".$j."'",$db);
$SaleQty=DB_fetch_array($TotalSaleQty);
?>
    <td class="tabledata"><?php echo $TotalEx['TOTALEXP']; ?></td>
    <td class="tabledata"><?php echo $SaleQty['TotalDispatch']; ?></td>
    <td class="tabledata"><?php echo $SaleQty['TotalAssValue']; ?></td>
<?php } ?>
  </tr>
  
<!------------------------- TOTAL AMOUNT ROW ---------------------------------------START HERE--------------------------> 


<?php } }else{?>

<tr class="row1">
<td class="tabledata1" colspan="13" style="color:#FF0000;">No Record Found</td>
</tr>

<?php } ?>
</table>
</div>
<?php } ?>
</div>
</div>
