<?php
if(isset($_POST['Save']))
{
$fin_year=$_REQUEST['fin_year'];
  $fromdate=ConvertSQLDate($_REQUEST['fdate']);
  $todate=ConvertSQLDate($_REQUEST['todate']);
  $month=explode('-',$_REQUEST['month']);
  
  $filter=array();
  
  if($_REQUEST['todate']!='' && $_REQUEST['fdate']!='' )
	{
		$filter[]="published_data between '".$fromdate."' and '".$todate."'";
	}
	if($_REQUEST['client']!='')
	{
	$filter[]="client_id='".$_REQUEST['client']."'";	
	}
if($month[0]!='000')
{
$filter[]="month='".$month[0]."'";
$filter[]="year='".$month[1]."'";
}
  
	if(count($filter)>0)
	{
	   $searchdata=implode(" and ", $filter);
 	   $sql1="select * from sale_budget where $searchdata";
	}
	else
	{
	 $sql1="select * from sale_budget where month='".date("m")."'";	
	}
	$_SESSION['query']=$sql1;
}	
	//echo $sql1;


?>

<link rel="stylesheet" href="css/new_mxerp.css">
<div class="body_contain">
<div class="tab_div">
<h1>Sale Budget V/s Actual Reports</h1>
 <form action=""  method="post">
<table width="100%" border="0" cellspacing="0" cellpadding="3">
<tr class="row1">
	<td class="tabledata1">Month Wise</td>
	<td><select name="month" id="month" style="width:150px;" >
	<option value="000">Select</option>
<?php $FinY=DB_query("SELECT *,YEAR(BookPeriodStart) AS SYear,YEAR(BookPeriodEnd) AS EYEAR FROM companies where CompanyId='".$_SESSION['ACGCompanyId']."'",$db);
      $FinYRow=DB_fetch_array($FinY);
	  $diffdata=($FinYRow['EYEAR']-$FinYRow['SYear'])+1;
	  for($i=0;$i<$diffdata;$i++){
	  if($i==0) { ?>
	<option value="4-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='4'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>April-<?php echo $FinYRow['SYear'] ?></option>
	<option value="5-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='5'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>May-<?php echo $FinYRow['SYear'] ?></option>
	<option value="6-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='6'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>Jume-<?php echo $FinYRow['SYear'] ?></option>
	<option value="7-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='7'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>July-<?php echo $FinYRow['SYear'] ?></option>
	<option value="8-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='8'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>August-<?php echo $FinYRow['SYear'] ?></option>
	<option value="9-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='9'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>September-<?php echo $FinYRow['SYear'] ?></option>
	<option value="10-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='10'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>Octoober-<?php echo $FinYRow['SYear'] ?></option>
	<option value="11-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='11'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>November-<?php echo $FinYRow['SYear'] ?></option>
	<option value="12-<?php echo $FinYRow['SYear'] ?>"<?php if($_REQUEST['month']=='12'.'-'.$FinYRow['SYear']){?>selected="selected"<?php }?>>December-<?php echo $FinYRow['SYear'] ?></option>
	<?php } else {?>
	<option value="1-<?php echo $FinYRow['EYEAR'] ?>"<?php if($_REQUEST['month']=='1'.'-'.$FinYRow['EYEAR']){?>selected="selected"<?php }?>>January-<?php echo $FinYRow['EYEAR'] ?></option>
	<option value="2-<?php echo $FinYRow['EYEAR'] ?>"<?php if($_REQUEST['month']=='2'.'-'.$FinYRow['EYEAR']){?>selected="selected"<?php }?>>February-<?php echo $FinYRow['EYEAR'] ?></option>
	<option value="3-<?php echo $FinYRow['EYEAR'] ?>"<?php if($_REQUEST['month']=='3'.'-'.$FinYRow['EYEAR']){?>selected="selected"<?php }?>>March-<?php echo $FinYRow['EYEAR'] ?></option>
	
	<?php } } ?>	
	</select></td>
	<?php	 
	?>
	 <td class="tabledata1">Party Wise</td>
	 <td><select name="client" style="width:150px;" id="client">
	 	<option value="">Select</option>
	 <?php
	  $sql="Select * from clientmaster where CompanyId='".$_SESSION['ACGCompanyId']."' and  Site in('".$_SESSION['AssignedBranch']."') and Zone in(".$_SESSION['AssignedSites'].") order by c_name ASC ";
    $result=DB_query($sql,$db);
    while($row=DB_fetch_array($result))
	{
	 ?>	
	 <option value="<?php echo $row['id']; ?>"<?php if($_REQUEST['client']==$row['id']) { ?>selected="selected" <?php } ?>><?php echo $row['c_name']; ?></option>
	 <?php } ?>
	 	</select>
	 </td>
</tr>
<tr class="row1">
	<td colspan="4">
		<p align="center"><input type="submit" name="Save" value="Search" class="search" /></p>
	</td>
</tr>
</table>
	
	</form>
	<?php 
if($_REQUEST['Save']!='')
{?>
	<h3>Showing For Client Sales Budget of    <?php if($_REQUEST['month']!='000' && $_REQUEST['client']==''){
		$monthdata=explode('-',$_REQUEST['month']);
		 $timestamp = mktime(0, 0, 0, $monthdata[0], 1, $monthdata[1]);
       echo date("M", $timestamp);  echo "   "; echo $monthdata[1]; } else {
          echo $s->GetFieldValue('clientmaster',"id='".$_REQUEST['client']."'",'c_name'); }  ?> 
      </h3>
      <div align="right"><a onclick='popup("salebudget_target.php?id=1&client=<?php $_REQUEST['client']; ?>&month=<?php echo $_REQUEST['month']; ?>",1000,1000)' href="#"   >Print</a></div>
<table width="100%" border="1" cellspacing="0" cellpadding="0">
  <tr class="row1">
      <td class="tabledata">S.No</td> 
    <?php if($_REQUEST['client']!=''){ ?>
    <td class="tabledata">Month</td>   <?php } else { ?>
    	<td class="tabledata">Party</td>
    	<?php } ?>
    <td class="tabledata">Target</td> 
    <td class="tabledata">Actual</td>  
     <td class="tabledata">Diff</td> 
  </tr>
  <?php 
  $query_target=DB_query($sql1,$db);
    $num=DB_num_rows($query_target);
    if(DB_num_rows($query_target)>0)
    {
    $j=1;
    $sql2=DB_query($sql1,$db);
  	while($Crm_sales=DB_fetch_array($sql2))
	{  	
	?>	
  <tr bgcolor="#c1c1c1">
  <td><?php echo $j; ?></td>
  <?php if($_REQUEST['client']!='') {?>
  	 <td> 
  	<?php
  	$monthdata=explode('-',$_REQUEST['month']);
		 $timestamp = mktime(0, 0, 0, $Crm_sales['month'], 1, $Crm_sales['year']);
       echo date("M", $timestamp);  echo "   "; echo $monthdata[1];  ?></td>
  	<?php } else { ?>
  <td> 
  <?php echo $s->GetFieldValue('clientmaster',"id='".$Crm_sales['client']."'",'c_name'); ?></center></td><?php } ?>
  <td><?php echo $Crm_sales['expected_order'] ?></td>
 <?php 
 $t=0;
 $Sales1=DB_query("select * from salesorder where client='".$Crm_sales['client']."'  ",$db);
 while($Sales1Row=DB_fetch_array($Sales1))
 {
 $invoice=DB_query("SELECT SUM(Quantity) AS Total FROM salesorder_details WHERE masterId='".$Sales1Row['id']."' and (ApprovalStatus='2' || ApprovalStatus='3')",$db);
 while($invoiceRow1=DB_fetch_array($invoice))
 {?>	
	<?php $t+= $invoiceRow1['Total'];?>
	<?php  } } ?>  
  <td><?php echo $t;?></td>
   <?php 	
	
	$DiffRow=$t-$Crm_sales['expected_order'];
	
	
	?>
 <td>
	<?php echo $DiffRow; ?></td>
  </tr>  
  <?php $j++; }  }
  else {?>
  <tr>
  <td colspan="5">No Records</td>	
  </tr>  
  <?php } ?>
      
 
  
</table>

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


