<link href="css/form.css" type="text/css" rel="stylesheet" media="screen"  />
<html>
<body >

<?
session_start();
include("opendb.php");
include("test.php");
if(strlen($user)<=0)
{
  print "<script> alert('Invalid User'); document.location='inde.php' </script>";
  return;
}
?>
<title>Sale Report</title>
<link rel="stylesheet" href="style.css" type="text/css" />
<script type="text/javascript" src="scripts/jquery-latest.js"></script>
<link type="text/css" href="includes/style1.css" rel="stylesheet" />
<link type="text/css" href="css/form.css" rel="stylesheet" />

<script type="text/javascript">
        $(function() {
          if ($.browser.msie && $.browser.version.substr(0,1)<7)
          {
			$('li').has('ul').mouseover(function(){
				$(this).children('ul').show();
				}).mouseout(function(){
				$(this).children('ul').hide();
				})
          }
        });        
    </script>
<script>
function valid()
{
}
</script>

<link rel="stylesheet" type="text/css" media="all" href="jsDatePick_ltr.min.css" />
<script type="text/javascript" src="jsDatePick.min.1.3.js"></script>

<script type="text/javascript">
	window.onload = function(){
		new JsDatePick({
			useMode:2,
			target:"inputField",
			dateFormat:"%Y-%m-%d"
			
		});
		new JsDatePick({
			useMode:2,
			target:"inputField1",
			dateFormat:"%Y-%m-%d"
			
		});
              
	};
</script>

<SCRIPT LANGUAGE="JavaScript" SRC="util/PopupWindow.js"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript" SRC="util/AnchorPosition.js"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript" SRC="util/CalendarPopup.js"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript" SRC="util/date.js"></SCRIPT>

<script>

 
var cal1 = new CalendarPopup();
cal1.showYearNavigation();

var cal2 = new CalendarPopup();
cal2.showYearNavigation();

  </script>
<style>
 th{
  

   // background-color: grey;  
    font-weight: bold;  
    color: white; 
    border:1px solid #D7D7D1;

    border-collapse:collapse;
   

background: rgb(181,189,200); /* Old browsers */
background: -moz-linear-gradient(top, rgba(181,189,200,1) 0%, rgba(130,140,149,1) 36%, rgba(40,52,59,1) 100%); /* FF3.6+ */
background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,rgba(181,189,200,1)), color-stop(36%,rgba(130,140,149,1)), color-stop(100%,rgba(40,52,59,1))); /* Chrome,Safari4+ */
background: -webkit-linear-gradient(top, rgba(181,189,200,1) 0%,rgba(130,140,149,1) 36%,rgba(40,52,59,1) 100%); /* Chrome10+,Safari5.1+ */
background: -o-linear-gradient(top, rgba(181,189,200,1) 0%,rgba(130,140,149,1) 36%,rgba(40,52,59,1) 100%); /* Opera 11.10+ */
background: -ms-linear-gradient(top, rgba(181,189,200,1) 0%,rgba(130,140,149,1) 36%,rgba(40,52,59,1) 100%); /* IE10+ */
background: linear-gradient(to bottom, rgba(181,189,200,1) 0%,rgba(130,140,149,1) 36%,rgba(40,52,59,1) 100%); /* W3C */
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#b5bdc8', endColorstr='#28343b',GradientType=0 ); /* IE6-9 */

 }

.ro_settled td
 {
background: #6bba70; /* Old browsers */
background: -moz-linear-gradient(top, #6bba70 0%, #6bba70 100%); /* FF3.6+ */
background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#6bba70), color-stop(100%,#6bba70)); /* Chrome,Safari4+ */
background: -webkit-linear-gradient(top, #6bba70 0%,#6bba70 100%); /* Chrome10+,Safari5.1+ */
background: -o-linear-gradient(top, #6bba70 0%,#6bba70 100%); /* Opera 11.10+ */
background: -ms-linear-gradient(top, #6bba70 0%,#6bba70 100%); /* IE10+ */
background: linear-gradient(to bottom, #6bba70 0%,#6bba70 100%); /* W3C */
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#6bba70', endColorstr='#6bba70',GradientType=0 ); /* IE6-9 */
color:white;
 }

.ro_settl td
 {
background: #6bba70; /* Old browsers */
background: -moz-linear-gradient(top, #6bba70 0%, #6bba70 100%); /* FF3.6+ */
background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#6bba70), color-stop(100%,#6bba70)); /* Chrome,Safari4+ */
background: -webkit-linear-gradient(top, #6bba70 0%,#6bba70 100%); /* Chrome10+,Safari5.1+ */
background: -o-linear-gradient(top, #6bba70 0%,#6bba70 100%); /* Opera 11.10+ */
background: -ms-linear-gradient(top, #6bba70 0%,#6bba70 100%); /* IE10+ */
background: linear-gradient(to bottom, #6bba70 0%,#6bba70 100%); /* W3C */
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#6bba70', endColorstr='#6bba70',GradientType=0 ); /* IE6-9 */
color:red;
 }



</style>
<body background="back_ground.jpg">
<?php //include('logo2.php'); ?>
<?php include('includes/top.php'); ?>
<form action='<?print $PHP_SELF?>' method='post' >

<br>
<table class="container"><tr><td>

<br>

<center>
<table class="form" style="width:auto;" align='center' >
<tr><th style="background:grey;">SALES REPORT</th></tr>
<tr><td>Show Report From <input type="text" id="inputField" value="<?echo date('Y-m-d');?>" name="fromdt" readonly  >
 To <input type="text" id="inputField1" name="todt" value="<?echo date('Y-m-d');?>" readonly > </td></tr>
<tr><td align='center'>Report by:<select name='type' ><option>All</option><option>RO Settled</option><option>Not Settled</option><option>Not Invoiced</option><option>List All</option></select>
<input type='submit' name='show' value='Show' ></td></tr></form>
</table>

<br><br>

<?
if($show)
{

       	
         $qry=mysqli_query($db,"select jcno,inv_date,invno,customer,regno from estentry where inv_date between '$fromdt' and '$todt' and invno>0 and brcode='$brcode' order by jcno");
         print "<table class='report' border='1'>";
	  print "<tr><th>Sl.no</th><th>Invdate</th>";
         $colspan=6;
         if($type=='RO Settled'){

             $temp_table='ro_settled'.$brcode.$user;
             mysqli_query($db,"drop table $temp_table");
             $sql="select distinct(estentry.jcno),estentry.inv_date, estentry.invno,estentry.customer,estentry.regno,max(down_payment.date) as dpr_date from estentry INNER JOIN down_payment ON estentry.jcno=down_payment.jcno where estentry.invno>0 and estentry.brcode='$brcode' and down_payment.brcode='$brcode' group by down_payment.jcno";
             mysqli_query($db,"CREATE table $temp_table AS ($sql)");

             print "<th>Settled date</th>";

             $qry=mysqli_query($db,"select * from $temp_table where  (dpr_date between '$fromdt' and '$todt') or (inv_date between '$fromdt' and '$todt')  order by  jcno  ");

            // echo "select * from $temp_table where   (dpr_date between '$fromdt' and '$todt') or (inv_date between '$fromdt' and '$todt')  order by  jcno  ";
             $colspan=7;
            
         }


         if($type=='Not Invoiced'){

             print "<th>Entry date</th>";
             $qry=mysqli_query($db,"select jcno,inv_date,invno,customer,regno,entrydate from estentry where entrydate between '$fromdt' and '$todt' and invno<=0 and brcode='$brcode' order by jcno");
             $colspan=7;
         }

if($type=='List All'){

             //print "<th>Entry date</th>";
             $qry=mysqli_query($db,"select jcno,inv_date,invno,customer,regno,entrydate from estentry where entrydate between '$fromdt' and '$todt'  and brcode='$brcode' order by jcno");

              
    $colspan=6;
         print "<th>Invno</th><th>1Jcno</th><th>Customer</th><th>Regno</th><th>Parts Total</th><th>Labour Total<th>Sublet Amt</th><th>Invoice Total</th><th>DPR Total</th><th>Parts Cost</th><th>Parts Profit</th>";
	  print "<tr><td colspan='14' align='left'><b>".$type."</b></td></tr>";
 $i=1;

	  $parts_tot=0;
	  $labour_tot=0;
	  $inv_tot=0;
	  $dpr_nettot=0;
	  $parts_cost=0;
	  $parts_profit=0;
         $sublet=0;
while($row35=mysqli_fetch_array($qry))
	  {  
 		
		$tot=0;
		$cost=0;
		$profit=0;
		$dpr_tot=0;
		$qry1=mysqli_query($db,"select sum(gtot),sum(pur_rate*qty) from jcardparts where jcno='$row35[0]' and brcode='$brcode'");
	


		$qry2=mysqli_query($db,"select sum(servamt),sum(subamt),sum(dis) from jcardserv where jcno='$row35[0]' and brcode='$brcode'");
		$row1=mysqli_fetch_array($qry1);
		$row2=mysqli_fetch_row($qry2);
		$tot=$row1[0]+$row2[0]-$row2[2];
		$cost=$row1[1];
		 $profit=$row1[0]-$row1[1];
		if(strlen($tot)<=0)
		    $tot=0;

		$qry3=mysqli_query($db,"select sum(amt),max(date) from down_payment where jcno='$row35[0]' and brcode='$brcode'");
		$row3=mysqli_fetch_row($qry3);
		$dpr_tot=$row3[0];






                

                     if ($row35[2]==0)

			   {

                             print "<tr class='ro_settl'>";  
$p++;
			   }
				else
{
                         print "<tr>"; 
$un++; 
 }             
		  
     if($dpr_tot>=$tot && $row35[2]>0 )
			   {
                         $max_date=max($row35[1],$row3[1]);
                     
                         if(($max_date >= $fromdt) && ($max_date <= $todt))
                           {
                              print "<tr class='ro_settled' >";  
$s++;
$un--; 
				}
                        } 
	print "<td>".$i."</td><td>$row35[1]</td><td>$row35[2]</td><td>$row35[0]</td><td>$row35[3]</td><td>$row35[4]</td><td align='right'>$row1[0]</td><td align='right'>$row2[0]</td><td align='right'>$row2[1]</td><td align='right'>".$tot."</td><td align='right'>".$dpr_tot."</td><td align='right'>".$cost."</td><td align='right'>".$profit."</td></tr>";
			$parts_tot+=$row1[0];
			$labour_tot+=$row2[0];
			$inv_tot+=$tot;
		       $dpr_nettot+=$row3[0];
			$parts_cost+=$cost;
			$parts_profit+=$profit;
                     $sublet+=$row2[1];
			$i++;
		  


}
$totro=$i-1;
 print "<tr><th align='right' colspan='".$colspan."'><b>Grand Total:</b></th><th align='right'>".putcoma($parts_tot)."</th><th align='right'>".putcoma($labour_tot)."</th><th align='right'>".putcoma($sublet)."</th><th align='right'>".putcoma($inv_tot)."</th><th align='right'>".putcoma($dpr_nettot)."</th><th align='right'>".putcoma($parts_cost)."</th><th align='right'>".putcoma($parts_profit)."</th></tr>";
 print "<tr><th align='right' colspan='".$colspan."'><b>Total RO's:</b></th><th align='right'>".putcoma($totro)."</th><th align='right' colspan='".$colspan."'></th></tr>";
 print "<tr><th align='right' colspan='".$colspan."'><b>Settled RO's:</b></th><th align='right'>".putcoma($s)."</th><th align='right' colspan='".$colspan."'></th></tr>";
 print "<tr><th align='right' colspan='".$colspan."'><b>Unsettled RO's:</b></th><th align='right'>".putcoma($un)."</th><th align='right' colspan='".$colspan."'></th></tr>";
 print "<tr><th align='right' colspan='".$colspan."'><b>Pending RO's:</b></th><th align='right'>".putcoma($p)."</th><th align='right' colspan='".$colspan."'></th></tr>";
		

    
return;
     }


         print "<th>Invno</th><th>Jcno</th><th>Customer</th><th>Regno</th><th>Parts Total</th><th>Labour Total<th>Sublet Amt</th><th>Invoice Total</th><th>DPR Total</th><th>Parts Cost</th><th>Parts Profit</th>";
	  print "<tr><td colspan='14' align='left'><b>".$type."</b></td></tr>";
     
        
        
	 
         $i=1;
	  $parts_tot=0;
	  $labour_tot=0;
	  $inv_tot=0;
	  $dpr_nettot=0;
	  $parts_cost=0;
	  $parts_profit=0;
         $sublet=0;
	  while($row=mysqli_fetch_array($qry))
	  {  

		$tot=0;
		$cost=0;
		$profit=0;
		$dpr_tot=0;
		$qry1=mysqli_query($db,"select sum(gtot),sum(pur_rate*qty) from jcardparts where jcno='$row[0]' and brcode='$brcode'");
	


		$qry2=mysqli_query($db,"select sum(servamt),sum(subamt),sum(dis) from jcardserv where jcno='$row[0]' and brcode='$brcode'");
		$row1=mysqli_fetch_array($qry1);
		$row2=mysqli_fetch_row($qry2);
		$tot=$row1[0]+$row2[0]-$row2[2];
		$cost=$row1[1];
		$profit=$row1[0]-$row1[1];
		if(strlen($tot)<=0)
		    $tot=0;

		$qry3=mysqli_query($db,"select sum(amt),max(date) from down_payment where jcno='$row[0]' and brcode='$brcode'");
		$row3=mysqli_fetch_row($qry3);
		$dpr_tot=$row3[0];

		if(strlen($dpr_tot)<=0)
		    $dpr_tot=0;

		
		if(($type=='RO Settled')&&($tot>0) && ($dpr_tot>=$tot))
	         {
                     $max_date=max($row[1],$row[5]);
                     
                     if(($max_date >= $fromdt) && ($max_date <= $todt))
                        {
		  		print "<tr><td>".$i."</td><td>$row[1]</td><td>$max_date</td><td>$row[2]</td><td>$row[0]</td><td>$row[3]</td><td>$row[4]</td><td align='right'>$row1[0]</td><td align='right'>$row2[0]</td><td align='right'>$row2[1]</td><td align='right'>".$tot."</td><td align='right'>".$dpr_tot."</td><td align='right'>".$cost."</td><td align='right'>".$profit."</td></tr>";
				$parts_tot+=$row1[0];
				$labour_tot+=$row2[0];
				$inv_tot+=$tot;
		       	$dpr_nettot+=$row3[0];
				$parts_cost+=$cost;
				$parts_profit+=$profit;
                    	       $sublet+=$row2[1];
                     	$i++;
                       }
		  }
		if(($type=='Not Settled')&&($tot>0) && ($dpr_tot<$tot))
	         {
		  	print "<tr><td>".$i."</td><td>$row[1]</td><td>$row[2]</td><td>$row[0]</td><td>$row[3]</td><td>$row[4]</td><td align='right'>$row1[0]</td><td align='right'>$row2[0]</td><td align='right'>$row2[1]</td><td align='right'>".$tot."</td><td align='right'>".$dpr_tot."</td><td align='right'>".$cost."</td><td align='right'>".$profit."</td></tr>";
			$parts_tot+=$row1[0];
			$labour_tot+=$row2[0];
	
			$inv_tot+=$tot;
		       $dpr_nettot+=$row3[0];
			$parts_cost+=$cost;
			$parts_profit+=$profit;
                     $sublet+=$row2[1];			
                     $i++;
		  }
		if(($type=='All')&&($tot>0))
	         {

                     if($dpr_tot>=$tot)
			   {
                         $max_date=max($row[1],$row3[1]);
                     
                         if(($max_date >= $fromdt) && ($max_date <= $todt))
                           {
                              print "<tr class='ro_settled' >";  
				}
                        } 
                     else
                         print "<tr>";  
              
		  	print "<td>".$i."</td><td>$row[1]</td><td>$row[2]</td><td>$row[0]</td><td>$row[3]</td><td>$row[4]</td><td align='right'>$row1[0]</td><td align='right'>$row2[0]</td><td align='right'>$row2[1]</td><td align='right'>".$tot."</td><td align='right'>".$dpr_tot."</td><td align='right'>".$cost."</td><td align='right'>".$profit."</td></tr>";
			$parts_tot+=$row1[0];
			$labour_tot+=$row2[0];
			$inv_tot+=$tot;
		       $dpr_nettot+=$row3[0];
			$parts_cost+=$cost;
			$parts_profit+=$profit;
                     $sublet+=$row2[1];
			$i++;
		  }


              if($type=='Not Invoiced')
	         {

  
		  	print "<tr><td>".$i."</td><td>$row[1]</td><td>$row[5]</td><td>$row[2]</td><td>$row[0]</td><td>$row[3]</td><td>$row[4]</td><td align='right'>$row1[0]</td><td align='right'>$row2[0]</td><td align='right'>$row2[1]</td><td align='right'>".$tot."</td><td align='right'>".$dpr_tot."</td><td align='right'>".$cost."</td><td align='right'>".$profit."</td></tr>";
			$parts_tot+=$row1[0];
			$labour_tot+=$row2[0];
			$inv_tot+=$tot;
		       $dpr_nettot+=$row3[0];
			$parts_cost+=$cost;
			$parts_profit+=$profit;
                     $sublet+=$row2[1];
			$i++;
		  }

	   }
           print "<tr><th align='right' colspan='".$colspan."'><b>Grand Total:</b></th><th align='right'>".putcoma($parts_tot)."</th><th align='right'>".putcoma($labour_tot)."</th><th align='right'>".putcoma($sublet)."</th><th align='right'>".putcoma($inv_tot)."</th><th align='right'>".putcoma($dpr_nettot)."</th><th align='right'>".putcoma($parts_cost)."</th><th align='right'>".putcoma($parts_profit)."</th></tr>";
		
}
?>