<?php 
error_reporting(0);
require_once("config/configDB.php");
require_once("config/constant.php");

$sql_customer = mysqli_query($conn,"SELECT sh.customer_id,cm.cname,cm.cname_tamil FROM saleheader as sh  join customer_master as cm on cm.id=sh.customer_id WHERE cm.isdelete='1' and sh.status='' GROUP BY sh.customer_id order by cm.cname asc");

$row_customer = '';




$tripfromDate=$_POST['fromDate'];
				$triptoDate=$_POST['toDate'];
				$ename=$_POST['ename'];
				$customer=$_POST['customer'];
				$area=$_POST['area'];
				$product=$_POST['product'];


?>
<!DOCTYPE html>
<html>
<head>
    <title>Sales Report</title>
    <?php require 'header.php' ?>
    <link href = "https://code.jquery.com/ui/1.10.4/themes/ui-lightness/jquery-ui.css"
         rel = "stylesheet">
<script >
var tableToExcel = (function() {
  var uri = 'data:application/vnd.ms-excel;base64,'
    , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
    , base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
    , format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) }
  return function(table, name) {
    if (!table.nodeType) table = document.getElementById(table)
    var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
    window.location.href = uri + base64(format(template, ctx))
  }
})()

</script>
</head>
<body>
    <?php require 'menu.php' ?>


    <div class="container text-center" id="saleStock">
  
  <form class="form-inline" method="post" action="stockReport.php">
    
    <div class="form-group">
      <label for="">From Date:</label><br>
      <input type="date" class="form-control input-lg " id="fromDate" name="fromDate" autocomplete="off">
    </div>
    <div class="form-group">
      <label for="">To Date</label><br>
      <input type="date" class="form-control input-lg" id="toDate" name="toDate" autocomplete="off">
    </div>

<div class="form-group">
    <label for="">Customer </label>

        <select class="form-control  customerStock selectpicker" data-show-subtext="true" data-live-search="true" name="customer">
        <option value="">Select the Customer </option>
        <?php
        while($row = mysqli_fetch_array($sql_customer)){
        ?>
        <option value="<?php echo $row['customer_id'] ?>" > <?php echo $row['cname']; ?>/<?php echo $row['cname_tamil']; ?> </option>
        <?php
        }
        ?>
        </select>
    </div>
    
    
    <div class="form-group">
    <label for="">Product </label>

        <select class="form-control  customerStock selectpicker" data-show-subtext="true" data-live-search="true" name="product">
        <option value="">Select the Product </option>
       <?php
	   $sqlproduct = mysqli_query($conn,"SELECT id, productname,product_tamil FROM product_master  ");



while($prow = mysqli_fetch_array($sqlproduct)){
	   
	   ?>
        <option value="<?php echo $prow['id'];?>" > <?php echo $prow['productname'];?>/<?php echo $prow['product_tamil'];?> </option>
       <?php
	   }
	   ?>
        </select>
    </div>
    
    
    <div class="form-group">
    <label for="">Area </label>

        <select class="form-control  customerStock selectpicker" data-show-subtext="true" data-live-search="true" name="area">
        <option value="">Select the Area </option>
       <?php
	   $sqlproduct = mysqli_query($conn,"SELECT id, areaname,area_tamil FROM area_master WHERE isdelete='1' ");



while($prow = mysqli_fetch_array($sqlproduct)){
	   
	   ?>
        <option value="<?php echo $prow['areaname'];?>" > <?php echo $prow['areaname'];?>/<?php echo $prow['area_tamil'];?> </option>
       <?php
	   }
	   ?>
        </select>
    </div>
    
    <!--<div class="form-group">
    <label for="">Employee </label>

        <select class="form-control  customerStock selectpicker" data-show-subtext="true" data-live-search="true" name="ename">
        <option value=""> Select the Employee </option>
        <?php
		$emp=mysqli_query($conn,"select ename from saleheader where status='' group by ename");
        while($rowe = mysqli_fetch_array($emp)){
        ?>
        <option value="<?php echo $rowe['ename'] ?>" > <?php echo $rowe['ename']; ?> </option>
        <?php
        }
        ?>
        </select>
    </div>
    <div class="form-group"><br>
    <label for="">Vehicle </label>

        <select class="form-control  customerStock selectpicker" data-show-subtext="true" data-live-search="true" name="vname">
        <option value=""> Select the Vehicle </option>
        <?php
       $veh=mysqli_query($conn,"select vehicleno from saleheader where status='' group by vehicleno");
        while($rowv = mysqli_fetch_array($veh)){
        ?>
        <option value="<?php echo $rowv['vehicleno'] ?>" > <?php echo $rowv['vehicleno']; ?> </option>
        <?php
        }
        ?>
        </select>
    </div>-->
     <div class="form-group"><br>

    <button type="submit" class="btn btn-primary btn-md datewise-submit-sale">Submit</button>

    <button type="button" class="btn btn-info btn-md datewise-submit-sale" onClick="tableToExcel('a', 'W3C Example Table')">Excel</button>
    </div>
  </form>
  
</div>


<div class="container col-sm-12">
&nbsp;
</div>
    



<div class="container col-sm-12">
<div id="a">
<table id="salestockDashboard" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Invoice No</th>
                <th>Date</th>
				
                <th>Customer</th>
                <th>Area</th>
               
                <!--<th>Vehicle.No</th>
                 
                <th>Employee Name</th>-->
                <th>Product</th>
               <!-- <th>Lot.No</th>-->
                <th>Rate</th>
				<th>Qty</th>
				
				
                <th>Net Amount</th>
               
                <!-- <th>Action</th> -->
            </tr>
        </thead>
        
        <?php
				
				if($tripfromDate ==''  && $triptoDate =='' && $customer ==''  && $area =='' && $product=='' ) {
				$sql = "SELECT sh.id,sh.invoice_no,sh.area,sh.area_tamil, sh.sale_date,sh.customer_id, sh.vehicleno as vehicle,sh.ename as ename, sh.itemcount, sh.tamount, sh.create_date,sh.id,cm.cname,cm.cname_tamil,sd.productID,sd.sale_prate prate,sd.sale_eqty qt,pm.productname pname,sd.salenetamt,sd.lot_number FROM saleheader as sh LEFT JOIN customer_master as cm on cm.id = sh.customer_id inner join saledetails as sd on sh.id=sd.saleheader_id inner join product_master as pm on pm.id=sd.productID where sh.flag='1' and sh.status='' order by sh.id DESC LIMIT 100 ";
				}
				else
				{
				
				$sql = "SELECT sh.id,sh.invoice_no,sh.area,sh.area_tamil, sh.sale_date,sh.customer_id, sh.vehicleno as vehicle,sh.ename as ename, sh.itemcount, sh.tamount, sh.create_date,sh.id,cm.cname,cm.cname_tamil,sd.productID,sd.sale_prate prate,sd.sale_eqty qt,pm.productname pname,sd.salenetamt,sd.lot_number FROM saleheader as sh LEFT JOIN customer_master as cm on cm.id = sh.customer_id inner join saledetails as sd on sh.id=sd.saleheader_id inner join product_master as pm on pm.id=sd.productID where sh.flag='1' and sh.status=''  ";
				
				}
				
				if($tripfromDate !=''  && $triptoDate !='' && $customer ==''  && $area =='' && $product=='' ) {
	 	$sql.=" and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 
	 else if($tripfromDate !=''  && $triptoDate !='' && $customer !=''  && $area =='' && $product=='') {
	  	$sql.=" and sh.customer_id='".$customer."'  and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 else if($tripfromDate !=''  && $triptoDate !='' && $customer !=''  && $area =='' && $product=='' ) {
		$sql.=" and sh.ename='".$ename."'  and sh.customer_id='".$customer."'  and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' ORDER BY sh.id,sh.sale_date  ASC ";
	 }
	 
	 else if($tripfromDate !=''  && $triptoDate !='' && $customer =='' && $area =='' && $product=='') {
		$sql.=" and sh.ename='".$ename."'   and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' ORDER BY sh.id,sh.sale_date  ASC ";

   	 }
	 
	 else if($tripfromDate ==''  && $triptoDate =='' && $customer !=''  && $area =='' && $product=='')  {
	 	$sql.="  and sh.customer_id='".$customer."'   ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 else if($tripfromDate ==''  && $triptoDate =='' && $customer ==''  && $ename !='' && $vno=='' && $product=='' )  {
		$sql.="  and sh.ename='".$ename."'   ORDER BY sh.id,sh.sale_date  ASC ";

	}
	
	else if($tripfromDate ==''  && $triptoDate =='' && $customer !=''  && $area =='' && $product=='')  {
		$sql.="  and sh.ename='".$ename."'  and sh.customer_id='".$customer."'   ORDER BY sh.id,sh.sale_date  ASC  ";

	}
	
	
	 
	 else if($tripfromDate !=''  && $triptoDate !='' && $customer =='' && $area =='' && $product!='' ) {
	 	$sql.=" and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' and sd.productID='".$product."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 
	  else if($tripfromDate !=''  && $triptoDate !='' && $customer !=''  && $area =='' && $product!='' ) {
	 	$sql.=" and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' and sd.productID='".$product."' and sh.customer_id='".$customer."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 
	 
	 else if($tripfromDate ==''  && $triptoDate =='' && $customer !=''  && $area =='' && $product!='' ) {
	 	$sql.="  and sd.productID='".$product."' and sh.customer_id='".$customer."'  ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 
	 
	 else if($tripfromDate ==''  && $triptoDate =='' && $customer ==''  && $area =='' && $product!='' ) {
	 	$sql.="  and sd.productID='".$product."'  ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 else if($tripfromDate !=''  && $triptoDate !='' && $customer !='' && $area !='' && $product!='' ) {
	 	$sql.=" and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."' and sd.productID='".$product."' and sh.customer_id='".$customer."' and  sh.area='".$area."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 else if($tripfromDate !=''  && $triptoDate !='' && $customer =='' && $area !='' && $product=='' ) {
	 	$sql.=" and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."'   and  sh.area='".$area."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 else if($tripfromDate !=''  && $triptoDate !='' && $customer !='' && $area !='' && $product=='' ) {
	 	$sql.=" and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."'   and  sh.area='".$area."' and sh.customer_id='".$customer."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 else if($tripfromDate !=''  && $triptoDate !='' && $customer =='' && $area !='' && $product!='' ) {
	 	$sql.=" and sale_date BETWEEN '".$tripfromDate."' AND '".$triptoDate."'   and  sh.area='".$area."' and sd.productID='".$product."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 else if($tripfromDate ==''  && $triptoDate =='' && $customer =='' && $area !='' && $product!='' ) {
	 	$sql.="and  sh.area='".$area."' and sd.productID='".$product."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 else if($tripfromDate ==''  && $triptoDate =='' && $customer !='' && $area !='' && $product=='' ) {
	 	$sql.="and  sh.area='".$area."' and sh.customer_id='".$customer."' ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	  else if($tripfromDate ==''  && $triptoDate =='' && $customer =='' && $area !='' && $product=='' ) {
	 	$sql.="and  sh.area='".$area."'  ORDER BY sh.id,sh.sale_date  ASC ";

	 }
	 
	 
	 
	 //echo $sql;
				?>
        
        <tbody>
            <?php
			$query=mysqli_query($conn, $sql) or die("brandgriddata.php: get brand Data step 1".$sql);
			$total_price = 0;
			$total_qty = 0;
			while($row=mysqli_fetch_array($query))
{
	$deliver_date=$row["sale_date"];
$date=date_create($deliver_date);
$sale_date2=date_format($date,"d-m-Y");
				
				?>
			<tr>
            <td><?php echo $row["invoice_no"];?></td>
            <td><?php echo $sale_date2;?></td>
			
            <td><?php echo $row["cname"];?>(<?php echo $row["cname_tamil"];?>)</td>
            <td><?php echo $row["area"];?>(<?php echo $row["area_tamil"];?>)</td>
            
           <!-- <td><?php echo $row["vehicle"];?></td>
           
            <td><?php echo $row["ename"];?></td>-->
            <td><?php echo $row["pname"];?></td>
            <!--<td><?php echo $row["lot_number"];?></td>-->
            <td><?php echo $row["prate"];?></td>
			<td align="right"><?php echo number_format((float)$row["qt"], 2, '.', '');?></td>
			
			
            <td align="right"><?php echo number_format(round($row["salenetamt"]), 2, '.', '');?></td>
			</tr>
            <?php
			
			$total_price  += $row["salenetamt"];
			$total_qty  += $row["qt"];
}


?>
       <tr>
           
           <td colspan="6" align="right"><strong>Total </strong></td>
            <td align="right"><strong><?php echo number_format((float)$total_qty, 2, '.', '');?></strong></td>
           
			
            <td align="right"><strong><?php echo number_format(round($total_price), 2, '.', '');?></strong></td>
			</tr>             
           
        </tbody>
       
    </table>
    </div>
</div>



    <?php require 'footer.php' ?>

    <script type="text/javascript">var base_url = "<?= $base_url ?>";</script>
  
    <script type="text/javascript" src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/jquery-ui.min.js"></script>

    <!--<script>



            $("#fromDate").datepicker({
        minDate: '-1Y-6M',
        maxDate: '+1Y+6M',
        dateFormat: 'yy-mm-dd',
        onSelect: function (dateStr) {
            var min = $(this).datepicker('getDate'); // Get selected date
            $("#toDate").datepicker('option', 'minDate', min || '0'); // Set other min, default to today
        }
    });

    //toDate

    $("#toDate").datepicker({
        minDate: '1',
        maxDate: '+1Y+6M',
        dateFormat: 'yy-mm-dd',
        onSelect: function (dateStr) {
            var max = $(this).datepicker('getDate'); // Get selected date

            $('#datepicker').datepicker('option', 'maxDate', max || '+1Y+6M'); // Set other max, default to +18 months

            
        }
    });

            


</script>-->
</body>
</html>