
<form action="" method="post">
<table width="100%" border="0" cellspacing="0" cellpadding="3">
  <tr class="row1">
    <td width="13%" align="right">Branch</td>
    <td width="35%"><select name="site" id="site" >
<?php 
  $site_sql=DB_query("Select loccode,prefix, locationname from locations",$db); 
while($site_row=DB_fetch_array($site_sql)){
?>
<option value="<?php echo $site_row['loccode'];?>"<?php echo ($_REQUEST['site']==$site_row['loccode']?'selected':'');?>><?php echo $site_row['locationname'];?></option>
<?php } ?>
</select></td>   
    <td width="20%" align="right">Year</td>
    <td><select name="payyear" >
 <?php
 
 for($i=date('Y');$i>2000;$i--)
 {
    $p='';
    if( $i==$_REQUEST['payyear'])
    $p='selected';
 ?><option value="<?php echo $i;?>" <?php echo $p;?>><?php echo $i;?></option>
 <?php 
 }
 ?></select></td>   
</tr>
<tr class="row1"> 
    <td colspan="4" style="text-align:center;"><input type="submit" name="Search" class="search" value="Search"/></td>
    </tr>
</table>    
</form>
<p align="right"><a href="annexuresale_exp.php" class="search">Export</a></p>
<?php $site_sql=DB_query("Select * from locations where prefix='".$_REQUEST['site']."'",$db);
$site_row=DB_fetch_array($site_sql);
?>

<div style="width:1250px; overflow:auto; margin:auto;">
<table width="2500" border="0" cellspacing="0" cellpadding="3" class="thborder">
  <tr>
    <th width="3%">C01</th>
    <th width="4%">Year &amp; (Month or Quarter) (YYYYMM or YYYYQQ) (C02)</th>
    <th width="4%">Buyer’s TIN (C03)</th>
    <th width="4%">Buyer’s Name (C04)</th>
    <th width="4%">Inter-State Branch Transfer  against F-form (C05)</th>
    <th>Inter-State Consignment Transfer against F-form (C06)</th>
    <th width="4%">Export Out side of India (C07)</th>
    <th width="4%">High Sea Sale (C08)</th>
    <th width="4%">ISS-Goods Type/ISS-Transaction Type (C09)</th>
    <th width="4%">ISS-Form Type (C10)</th>
    <th width="4%">ISS-Rate of Tax (C11)</th>
    <th width="4%">ISS-Sales Price (Excluding CST) (C12)</th>
    <th width="4%">ISS-Central Sales Tax (C13)</th>
    <th width="4%">ISS-Total (C14)</th>
    <th width="4%">Local Sale-Type of Sale (C15)</th>
    <th width="4%">Local Sale-Rate of Tax (C16)</th>
    <th width="4%">Local Sale-Sales Price (Excluding VAT) (C17)</th>
    <th width="4%">Local Sale-Output Tax (C18)</th>
    <th width="4%">Local Sale-Total (including VAT) (C19)</th>
    <th width="4%">Rate of Tax on the Item under Delhi Value Added Tax Act,2004 (C20)</th>
    <th width="4%">Sale of Petrol/Diesel Suffered Tax on Full Sale Price at OMC level (C21)</th>
    <th width="4%">Charges towards labour,services and other like charges in civil works contracts(C22)</th>
    <th width="4%">Charges towards cost of land,if any, in services civil works contracts (C23)</th>
    <th width="3%">Sales Against H-form to Delhi dealer (C24)</th>
    <th width="3%">Charges towards cost of land,if any, other than services civil works contracts (C25)</th>
  </tr>
  <?php 
if($_REQUEST['Search'])
{
    $month_array=array("01"=>"04","02"=>"04","03"=>"04","04"=>"01","05"=>"01","06"=>"01","07"=>"02","08"=>"02","09"=>"03","10"=>"03","11"=>"03","12"=>"03");
   $paystatus=true;  
   $site=$_REQUEST['site'];
   $years=$_REQUEST['payyear'];
   $month=$_REQUEST['paymonth'];

 $filter='';
 if($site!='All' )
   $filter[]=" branch_name='".$site."' ";
 if($years!='All' )
   $filter[]=" year(invoiceDate)='".$years."' ";
if(!empty($filter))
{
    $filter1=implode(" and ",$filter);
}    
   // $query1="select  * from purchase_billbook  where $filter1 group by vendor  order by bill_book_date desc,vendor ASC";

   $query_sql="select client_id,invoiceDate as bill_book_date from invoice  where ".$filter1." and invoiceDate between '".$_SESSION['financialYrStart']."' 
              and '".$_SESSION['financialYrEnd']."' group by client_id ORDER BY client_id ,month(invoiceDate) ASC  ";


//$_SESSION['Annexure']=$query1;
    $result1=DB_query($query_sql,$db);
    $numrows = DB_num_rows($result1);
    $assess_array=array();
    $charge_array=array();
    $gtotal_array=array();
    $i=1;           
    if($numrows>0)
    {
        while($myrow=DB_fetch_array($result1) )
        {      
         $time=strtotime($myrow['bill_book_date']);
         $month=date("m",$time);
         if($month)
         $year=date("Y",$time).$month_array[$month];    
         $yeardata[]=$year;         
         
         $C05='0.00'; 
        
         $C06='0.00';
         // $C07 values will be come from job work bill
         $C07="0.00";    
         $vendorid[]=$myrow['client_id'];     
          $tinno=$s->getField('clientmaster','id',$myrow['client_id'],'tin_no');
         $vendor_name=$s->getField('clientmaster','id',$myrow['client_id'],'c_name');
         
         $C08='0.00';
           $cform=$s->getField('form_type_master','form_name','FORM C','id');
         $hform=$s->getField('form_type_master','form_name','FORM H','id');
         $Wform=$s->getField('form_type_master','form_name','NONE','id');
        
         $withoutform=$s->GetFieldValue("invoice","client_id='".$myrow['client_id']."'  and formtype='".$cform."'","invoice_id");
        if($withoutform!='') 
         $C09='OT';
         else
         $C09=''; 
       
         $form10=$s->GetFieldValue("invoice","client_id='".$myrow['client_id']."'  and formType='".$cform."'","formType"); 
         if($form10!='') $C10='FORM C'; else $C10='';
         $C11=$s->GetFieldValue("invoice","client_id='".$myrow['client_id']."'  and formType='".$cform."'","sum(GrandTotal)");    
         if($C11=='') $C11='0.00';
         $vendor_state=$s->GetFieldValue("suppliers","id='".$myrow['vendor']."' ","state");    
         $state=$s->GetFieldValue("state_master","id='".$vendor_state."' ","state");
         if($state!='DELHI') 
         {   
         $Ctaxableamt=$s->GetFieldValue("invoice","client_id='".$myrow['client_id']."'  and formtype='".$cform."'","sum(taxableAmount)"); 
         $Chardware_amt=$s->GetFieldValue("invoice","client_id='".$myrow['client_id']."'  and formtype='".$cform."'","sum(hardware_amt)"); 
         $C12=$Chardware_amt+$Ctaxableamt;

         }
         if($C12=='') $C12='0.00';

         if($state!='DELHI') 
         {   
         $Ctaxableamt1=$s->GetFieldValue("invoice","client_id='".$myrow['client_id']."'  and formtype='".$cform."'","sum(VatCstValue)"); 
         $Chardware_amt1=$s->GetFieldValue("invoice","client_id='".$myrow['client_id']."'  and formtype='".$cform."'","sum(vat_cst_amt_hrd)");  
         $C13=$Ctaxableamt1+$Chardware_amt1;    
         }
         
         if($C13=='') $C13='0.00';
         $C14=$C12+$C13;
         
         if($withoutform=='')
         $C15='GD';
         else
         $C15='';
         $vatcst_asset=$s->GetFieldValue("invoice","client_id='".$myrow['client_id']."' ","VatCst_ledger");
         $C16=$s->getField('sales_tax_master','salestax_id',$vatcst_asset,'taxrate');
         $Ctaxableamt16=$s->GetFieldValue("invoice","client_id='".$myrow['client_id']."'  ","sum(taxableAmount)"); 
         $Chardware_amt16=$s->GetFieldValue("invoice","client_id='".$myrow['client_id']."'  ","sum(hardware_amt)");
         $C17=$Ctaxableamt16+$Chardware_amt16;
         if($C17=='') $C17='0.00';
          $Ctaxableamt18=$s->GetFieldValue("invoice","client_id='".$myrow['client_id']."'  ","sum(VatCstValue)"); 
         $Chardware_amt18=$s->GetFieldValue("invoice","client_id='".$myrow['client_id']."'  ","sum(vat_cst_amt_hrd)"); 
         $C18=$Ctaxableamt18+$Chardware_amt18;
         if($C18=='') $C18='0.00';
         $C19=$C17+$C18;
         $C20=$C16;
        
         $C21='0.00';
         $C22='0.00';
         $C23='0.00';
         $C24=$C22+$C23;
         $C25='0.00';

        $data_array[$year][]=array("year"=>$year,"TIN"=>$tinno,"vendor_name"=>$vendor_name,"C05"=>$C05,"C06"=>$C06,"C07"=>$C07,"C08"=>$C08,"C09"=>$C09,"C10"=>$C10,"C11"=>$C11,"C12"=>$C12,"C13"=>$C13,"C14"=>$C14,"C15"=>$C15,"C16"=>$C16,"C17"=>$C17,"C18"=>$C18,"C19"=>$C19,"C20"=>$C20,"C21"=>$C21,"C22"=>$C22,"C23"=>$C23,"C24"=>$C24,"C25"=>$C25);
       }
     }

       $locaion_code=$s->GetFieldValue("locations","locationname like '%Delhi%'","loccode");
       
        if($_REQUEST['site']==$locaion_code)
        {  

       $query_sql1="select sitecode_to,sum(grand_total),issuedate from transfer_issue where sitecode='".$locaion_code."'
                    AND sitecode_to!='".$locaion_code."' and issuedate between '".$_SESSION['financialYrStart']."' 
                    and '".$_SESSION['financialYrEnd']."' and year(issuedate)='".$years."'  ORDER BY sitecode_to,issuedate ASC  ";


          //$_SESSION['Annexure']=$query1;
          $result1=DB_query($query_sql1,$db);
          $numrows = DB_num_rows($result1);
          $assess_array=array();
          $charge_array=array();
          $gtotal_array=array();
          $i=1;           
          if($numrows>0)
          {
              while($myrow1=DB_fetch_array($result1) )
              {
                if($myrow1['issuedate']!='')
                {
                   $time=strtotime($myrow1['issuedate']);
                   $month=date("m",$time);
                   if($month)
                   $year=date("Y",$time).$month_array[$month];    
                   $yeardata[]=$year;
         
           $fform=$hform=$s->getField('form_type_master','form_name','FORM F','id');
           $C05=$s->GetFieldValue("transfer_issue","sitecode='".$locaion_code."' and form_type='".$fform."'","sum(grand_total)"); 
        
           $C06='0.00';
           // $C07 values will be come from job work bill
           $C07="0.00";    
            $vendorid[]=$myrow['vendor'];     
            $tinno=$s->getField('locations','loccode',$myrow1['sitecode_to'],'tin_no');
           $vendor_name=$s->getField('companies','CompanyId',$_SESSION['ACGCompanyId'],'CompanyName')."-".$s->getField('locations','loccode',$myrow1['sitecode_to'],'locationname');
           
           $C08='0.00';

           $C09='0.00';
           $cform=$s->getField('form_type_master','form_name','FORM C','id');
           $hform=$s->getField('form_type_master','form_name','FORM H','id');
           $Wform=$s->getField('form_type_master','form_name','NONE','id');
           $form10=$s->GetFieldValue("transfer_issue","sitecode='".$locaion_code."'  and form_type='".$cform."'","id"); 
           if($form10!='') $C10='FORM C'; else $C10='';
           $C11=$s->GetFieldValue("purchase_billbook","vendor='".$myrow1['vendor']."' and po_cat='MT' and form_type='".$cform."'","sum(GrandTotal)");    
           if($C11=='') $C11='0.00';
            $vendor_state=$s->GetFieldValue("suppliers","id='".$myrow1['vendor']."' ","state");    
           $state=$s->GetFieldValue("state_master","id='".$vendor_state."' ","state");
            $C12='0.00';
           $C13='0.00';
           $C14='0.00';
           $C15='0.00';          
           $C16='';
           $C17='0.00';
           $C18='0.00';
           $C19=$C17+$C18;
          
          
           $C21='';
           $C22='0.00';
          $C23='0.00';
           $C24=$C22+$C23;
           

          $data_array[$year][]=array("year"=>$year,"TIN"=>$tinno,"vendor_name"=>$vendor_name,"C05"=>$C05,"C06"=>$C06,"C07"=>$C07,"C08"=>$C08,"C09"=>$C09,"C10"=>$C10,"C11"=>$C11,"C12"=>$C12,"C13"=>$C13,"C14"=>$C14,"C15"=>$C15,"C16"=>$C16,"C17"=>$C17,"C18"=>$C18,"C19"=>$C19,"C20"=>$C20,"C21"=>$C21,"C22"=>$C22,"C23"=>$C23,"C24"=>$C24,"C25"=>$C25);

          }
         }
       }
}
 /* function super_unique($array)
{
  $result = array_map("unserialize", array_unique(array_map("serialize", $array)));

  foreach ($result as $key => $value)
  {
    if ( is_array($value) )
    {
      $result[$key] = super_unique($value);
    }
  }

  return $result;
}*/
//if(!empty($yeardata))
//$yeardata = super_unique($yeardata); 
//array_unique($yeardata);  
//echo "<pre>";
//print_r($data_array);

?>
  <?php
if(!empty($yeardata))
{ 
$i=0;
foreach($yeardata as $y=>$v)
{

    ?>
  <tr>
    <td><?php echo ($i+1); ?></td>     
    <td><?php echo $data_array[$v][$i][year]; ?></td>
    <td><?php echo $data_array[$v][$i][TIN]; ?></td>
    <td><?php echo $data_array[$v][$i][vendor_name]; ?></td>
    <td><?php echo $C05_data[]=$data_array[$v][$i][C05]; ?></td>
    <td><?php echo $C06_data[]=$data_array[$v][$i][C06]; ?></td>
    <td><?php echo $C07_data[]=$data_array[$v][$i][C07]; ?></td>
    <td><?php echo $C08_data[]=$data_array[$v][$i][C08]; ?></td>
    <td><?php echo $C09_data[]=$data_array[$v][$i][C09]; ?></td>
    <td><?php echo $C10_data[]=$data_array[$v][$i][C10]; ?></td>
    <td><?php echo $C11_data[]=$data_array[$v][$i][C11]; ?></td>
    <td><?php echo $C12_data[]=$data_array[$v][$i][C12]; ?></td>
    <td><?php echo $C13_data[]=$data_array[$v][$i][C13]; ?></td>
    <td><?php echo $C14_data[]=$data_array[$v][$i][C14]; ?></td>
    <td><?php echo $C15_data[]=$data_array[$v][$i][C15]; ?></td>
    <td><?php echo $C16_data[]=$data_array[$v][$i][C16]; ?></td>
    <td><?php echo $C17_data[]=$data_array[$v][$i][C17]; ?></td>
   <td><?php echo $C18_data[]=$data_array[$v][$i][C18]; ?></td>
   <td><?php echo $C19_data[]=$data_array[$v][$i][C19]; ?></td>
   <td><?php echo $data_array[$v][$i][C20]; ?></td>
   <td><?php echo $C21_data[]=$data_array[$v][$i][C21]; ?></td>
   <td><?php echo $C22_data[]=$data_array[$v][$i][C22]; ?></td>
   <td><?php echo $C23_data[]=$data_array[$v][$i][C23]; ?></td>
   <td><?php echo $data_array[$v][$i][C24]; ?></td>
   <td><?php echo $C25_data[]=$data_array[$v][$i][C25]; ?></td> 
  </tr>
   <?php $i++; }  }?>
  <tr>
     <td></td>
     <td></td>
     <td></td>
     <td></td>
     <td><?php if(!empty($C05_data)) echo array_sum($C05_data); ?></td>
     <td><?php if(!empty($C06_data)) echo array_sum($C06_data); ?></td>
     <td><?php if(!empty($C07_data)) echo array_sum($C07_data); ?></td>
     <td><?php if(!empty($C08_data)) echo array_sum($C08_data); ?></td>
     <td></td>
     <td> </td>
     <td><?php if(!empty($C11_data)) echo array_sum($C11_data); ?></td>
     <td><?php if(!empty($C12_data)) echo array_sum($C12_data); ?></td>
     <td><?php if(!empty($C13_data)) echo array_sum($C13_data); ?></td>
     <td><?php if(!empty($C14_data)) echo array_sum($C14_data); ?></td>
     <td><?php if(!empty($C15_data)) echo array_sum($C15_data); ?></td>
     <td><?php if(!empty($C16_data)) echo array_sum($C16_data); ?></td>
     <td><?php if(!empty($C17_data)) echo array_sum($C17_data); ?></td>
     <td><?php if(!empty($C18_data)) echo array_sum($C18_data); ?></td>
     <td><?php if(!empty($C19_data)) echo array_sum($C19_data); ?></td>
     <td>0.00</td>
     <td><?php if(!empty($C21_data)) echo array_sum($C21_data); ?></td>
     <td><?php if(!empty($C22_data)) echo array_sum($C22_data); ?></td>
     <td><?php if(!empty($C23_data)) echo array_sum($C23_data); ?></td>     
     <td>0.00</td>
     <td><?php if(!empty($C25_data)) echo array_sum($C25_data); ?></td>     
  </tr>
  <?php 
$_SESSION['Annexure_purchase']=$data_array;
$_SESSION['Annexure_year']=$yeardata;
 } ?>
</table>
</div>

 