<?php 
include 'dheader.php'; 
?>
	<div class="modal fade hidden-print" id="myModal" tabindex="-1" role="dialog" aria-hidden="true"></div>
	<div class="wrapper ">
		<?php include 'leftbar.php'; ?>
		<!-- left-bar -->
		<?php include 'topbar.php'; ?>
			<!-- top-bar -->
			<div class="main-content">

<form action="" method="get">
<div class="manage_buttons hidden-print">
	<div class="row">
	<div class="col-md-3">
			<div class="input-group input-daterange" id="reportrange2">	
                <span class="input-group-addon bg">Type</span>
                <select name="type" class=" form-control" id="search-select">
					<option value="Sale-Return" <?php echo ($_GET['type'] == 'Sale') ? 'selected':''; ?>>Sales & Returns</option>
					<option value="Purchase-PReturn" <?php echo ($_GET['type'] == 'Sale') ? 'selected':''; ?>>Purchases & Returns</option>
                	<!-- <option value="All" <?php echo ($_GET['type'] == 'All') ? 'selected':''; ?>>All</option> -->
                	<!-- <option value="Sale" <?php echo ($_GET['type'] == 'Sale') ? 'selected':''; ?>>Sale</option> -->
                	<!-- <option value="Purchase" <?php echo ($_GET['type'] == 'Purchase') ? 'selected':''; ?>>Purchase</option> -->
                	<!-- <option value="Return" <?php echo ($_GET['type'] == 'Return') ? 'selected':''; ?>>Sales Return</option> -->
                	<!-- <option value="PReturn" <?php echo ($_GET['type'] == 'PReturn') ? 'selected':''; ?>>Purchase Return</option> -->
                	<!-- <option value="Misc" <?php echo ($_GET['type'] == 'Misc') ? 'selected':''; ?>>Misc</option> -->
                </select>
            </div>
		</div>
		<?php 
			$showTableData = "";
			if($_GET['type'] == "Sale-Return")
			{
				$showTableData = "customerinvoice";
			}
			if($_GET['type'] == "Purchase-PReturn")
			{
				$showTableData = "purchases";
			}
		?>
		<div class="col-md-3">
			<div class="input-group input-daterange" id="reportrange2">	
                <span class="input-group-addon bg">By</span>
                <select name="by" class=" form-control" id="search-select">
                	<option value="All" <?php echo ($_GET['by'] == 'All') ? 'selected':''; ?>>All</option>
                	<option value="Credit" <?php echo ($_GET['by'] == 'Credit') ? 'selected':''; ?>>Credit</option>
                	<option value="Cash" <?php echo ($_GET['by'] == 'Cash') ? 'selected':''; ?>>Cash</option>
                </select>
            </div>
		</div>

		
		<div class="col-md-2">
			<div class="input-group input-daterange" id="reportrange">
                <span class="input-group-addon bg">
                   From					                       	</span>
                <input type="text" class="form-control start_date datepicker" value="<?php echo $_GET['sdate']; ?>" name="sdate" id="sdate">
            </div>
		</div>
		<div class="col-md-2">
			<div class="input-group input-daterange" id="reportrange1">
                <span class="input-group-addon bg">
                    To			                                </span>
                <input type="text" value="<?php echo $_GET['edate']; ?>" class="form-control end_date datepicker" name="edate" id="edate">
            </div>	
		</div>
		<div class="col-md-2">
			<div class="input-group input-daterange" id="reportrange1">
		<button type="submit" title="Submit" class="btn btn-success">Submit</button>
			</div>
		</div>	
	</div>
</div>

</form>
	<div class="container-fluid">
		<div class="row manage-table">

	
			<div class="panel panel-piluku">
				<div class="panel-heading">
				<h3 class="panel-title">
				<?php
				if (isset($_GET['sdate'])) {
				
					$sdate = $_GET['sdate'];
					$scus_date = explode('/', $sdate);
					$sget_sdate = $scus_date[1].'/'.$scus_date[0].'/'.$scus_date[2];

					// End date
	             	$edate = $_GET['edate'];
					$ecus_date = explode('/', $edate);
					$get_edate = $ecus_date[1].'/'.$ecus_date[0].'/'.$ecus_date[2];
				}
				?>
					Reports - Sales Report	<?php echo @$sget_sdate; ?>-<?php echo @$get_edate; ?>		
					<span class="panel-options custom">
											</span>
				</h3>
			</div>
				<div class="panel-body nopadding table_holder table-responsive" >
					<table class="tablesorter table  table-hover" id="sortable_table">
						<thead>
							<tr>
								<th>Date</th>
								<th>Name</th>
								<th>User</th>
								<th>Invoice#</th>
								<th>Sale Type</th>
								<th>Pay Type</th>
								<th>Subtotal</th>
								<th>Received</th>
								<th>Grand Total</th>
								<th>Transport</th>
								<th>Remaining</th>
								<th>Profit</th>
								
							</tr>
						</thead>
						<tbody>
						<?php 
						if (isset($_GET['sdate'])) {
						?>
						<?php
							// Start date
							$sdate = $_GET['sdate'];
							$scus_date = explode('/', $sdate);
							$get_sdate = $scus_date[2].'/'.$scus_date[0].'/'.$scus_date[1];

							// End date
			             	$edate = $_GET['edate'];
							$ecus_date = explode('/', $edate);
							$get_edate = $ecus_date[2].'/'.$ecus_date[0].'/'.$ecus_date[1];

							// DISTINCT INVOICES
							$disInv = "SELECT DISTINCT invoiceNo FROM $showTableData WHERE php_date BETWEEN '".$get_sdate."' AND '".$get_edate."'  AND payment_type != 'Opening Balance' ";
							if ($_GET['by'] == 'All') {
								$disInv .= "AND payment_type IN ('Credit', 'Cash') ";
							}else{
								$disInv .= "AND payment_type = '".$_GET['by']."' ";
							}
							if ($_GET['type'] == 'All') {
								$disInv .= "AND sale_type IN ('Sale', 'Return', 'Purchase', 'PReturn') ";
							}else{
								$exp_types = explode("-", $_GET['type']);
								$disInv .= "AND sale_type IN ('".$exp_types[0]."', '".$exp_types[1]."') ";
							}
							$disInv .= "ORDER BY invoiceNo ";
							$disInv = $db->query($disInv);

							$i = 0;
							$invoices = 0;
							$grandtotal = 0;
							$totalSales = 0;
							$totalReturns = 0;
							$totalPurchases = 0;
							$totalPReturns = 0;
							$totalprofit = 0;
							$grandexp = 0;
							$granddeb = 0;
							while($fInv = $disInv->fetchArray()){ 
							$i++;
		            		$invoices = $i;
							
							$sql = "SELECT * FROM $showTableData WHERE invoiceNo = '".$fInv['invoiceNo']."' AND php_date BETWEEN '".$get_sdate."' AND '".$get_edate."' AND payment_type != 'Opening Balance'";
							if ($_GET['by'] == 'All') {
								$sql .= "AND payment_type IN ('Credit', 'Cash') ";
							}else{
								$sql .= "AND payment_type = '".$_GET['by']."' ";
							}
							if ($_GET['type'] == 'Sale-Return') {
								$sql .= "AND sale_type IN ('Sale', 'Return')";
							}else{
								$exp_types2 = explode("-", $_GET['type']);
								$sql .= "AND sale_type IN ('".$exp_types2[0]."', '".$exp_types2[1]."') ";
							}
							
		            		$run = $db->query($sql);
		            		
		            		$row = $run->fetchArray(); 
		            		$grandtotal += $row['grand_total'];

		            		$sql2 = $db->query("SELECT * FROM $showTableData WHERE invoiceNo = '".$fInv['invoiceNo']."' AND payment_type != 'Opening Balance'");
                            
                            $profit = 0;
                            $totalCost = 0;
		            		while($row2 = $sql2->fetchArray()){
								$totalCost += ($row2['sell'] - $row2['purchase']) * $row2['qty'];
								
								if($row2['discount_pr'])
								{
									$profit += ( ($row2['total']) - ($row2['purchase'] * $row2['qty']) );
								}
								if($row['discount_rs'])
								{
									//  - $row2['discount_rs']
									$profit += $totalCost;
								}
								if($row['entire_discount_rs'] == '' && $row2['discount_pr'] == '' && $row['discount_rs'] == ''){
									$profit += $totalCost;
								}
		            		}
							if($row['entire_discount_rs'])
							{
								$profit += $totalCost - $row['entire_discount_rs'];
							}

							if($row['sale_type'] == 'Sale'){ 
								$totalprofit += round($profit,2); 
								$totalSales += round($row['grand_total'],2);
							}
							if($row['sale_type'] == 'Return'){ 
								$totalprofit -= abs(round($profit,2)); 
								$totalReturns += round($row['grand_total'],2);
							}
							if($row['sale_type'] == 'Purchase'){ 
								$totalPurchases += round($row['grand_total'],2);
							}
							if($row['sale_type'] == 'PReturn'){ 
								$totalPReturns += round($row['grand_total'],2);
							}
			            ?>    
			            	<tr>
			            		<td><?php echo $row['show_date']; ?></td>
			            		<td><?php echo $row['name_word']; ?></td>
			            		<td><?php echo $row['user_name']; ?></td>
			            		<td><?php echo $row['invoiceNo']; ?></td>
			            		<td><?php echo $row['sale_type']; ?></td>
			            		<td><?php echo $row['payment_type']; ?></td>
			            		<td><?php echo round($row['subtotal'],2); ?></td>
			            		<td><?php echo ($row['received']) ? round($row['received'],2) : 0; ?></td>
			            		<td><?php echo round($row['grand_total'],2); ?></td>
			            		<td><?php echo ($row['transport_expense']) ? $row['transport_expense'] : 0; ?></td>
			            		<td><?php echo round($row['remaining'],2); ?></td>
			            		<?php if($exp_types[0] == 'Sale' && $exp_types[1] == 'Return'){ ?>
									<td 
									<?php 
										if($row['sale_type'] == 'Sale'){ echo 'style="color:green"'; }
										if($row['sale_type'] == 'Return'){ echo 'style="color:red"'; } ?>><?php echo round($profit,2); ?></td>
								<?php }else{ ?>
									<td><?php echo "-"; ?></td>
								<?php } ?>
			            	</tr>
			            <?php }
			            
			            	$sdate = $_GET['sdate'];
							$scus_date = explode('/', $sdate);
							$get_sdate = $scus_date[2].'/'.$scus_date[0].'/'.$scus_date[1];

							// End date
			             	$edate = $_GET['edate'];
							$ecus_date = explode('/', $edate);
							$get_edate = $ecus_date[2].'/'.$ecus_date[0].'/'.$ecus_date[1];
                             $sql = "SELECT * FROM expenses WHERE php_date BETWEEN '".$get_sdate."' AND '".$get_edate."' ORDER BY php_date, id ";   
                             	$run = $db->query($sql);
			                while($row = $run->fetchArray()){
			                    	$grandexp += $row['payment'];
			            ?>
			                <tr>
			            		<td><?php echo $row['show_date']; ?></td>
			            		<td><?php echo $row['title']; ?></td>
			            		<td>-</td>
			            		<td><?php echo $row['id']; ?></td>
			            		<td>-</td>
			            		<td>-</td>
			            		<td>-</td>
			            		<td>-</td>
			            		<td><?php echo $row['payment']; ?></td>
			            		<td>-</td>
			            		<td>-</td>
			            		<td>-</td>
			            	</tr>
			            <?php
			               } // End of Expenses bracket
			                
			                $sdate = $_GET['sdate'];
							$scus_date = explode('/', $sdate);
							$get_sdate = $scus_date[2].'/'.$scus_date[0].'/'.$scus_date[1];

							// End date
			             	$edate = $_GET['edate'];
							$ecus_date = explode('/', $edate);
							$get_edate = $ecus_date[2].'/'.$ecus_date[0].'/'.$ecus_date[1];
                             $sql = "SELECT * FROM other_accounts WHERE title = 'Profit' AND php_date BETWEEN '".$get_sdate."' AND '".$get_edate."' ORDER BY php_date, id ";   
                             	$run = $db->query($sql);
			                while($row = $run->fetchArray()){
			                    	$granddeb += $row['receive'];
			                    	//$grandcre += $row['payment'];
			            
			            ?>
			                <tr>
			            		<td><?php echo $row['show_date']; ?></td>
			            		<td><?php echo $row['title']; ?></td>
			            		<td>-</td>
			            		<td><?php echo $row['id']; ?></td>
			            		<td>-</td>
			            		<td>-</td>
			            		<td>-</td>
			            		<td>-</td>
			            		<td>-</td>
			            		<td>-</td>
			            		<td>-</td>
			            		<td><?php echo $row['receive']; ?></td>
			            	</tr>
			            <?php
			                } // End of Other Accounts bracket
			            
			            }?>
			            
			            
			            
			            
			            
			            
			            	<tr>
								<?php 
									$exp_types = explode("-", $_GET['type']);
								?>
								<td colspan="12" align="center" style="color: green;">Total Invoices : <?php echo $invoices; ?> 
								<br> Total <?php echo $_GET['type']; ?> amount : <?php echo ($grandtotal); ?>
								<?php if($exp_types[0] == 'Sale' && $exp_types[1] == 'Return'){ ?>
								

									<br> Total Sales : <?php echo $totalSales; ?> 
									<br> Total Returns : <?php echo $totalReturns; ?> 




									<br><br> Total Expense : <?php echo $grandexp; ?> 
									<br> Total Profit : <?php echo (round($totalprofit,2) - $grandexp) + $granddeb; ?>
								<?php } ?>
	
								<?php if($exp_types[0] == 'Purchase' && $exp_types[1] == 'PReturn'){ ?>
									<br> Total Purchases : <?php echo $totalPurchases; ?> 
									<br> Total Purchase Returns : <?php echo $totalPReturns; ?> 
								<?php } ?>

								<br> Grand Total : <?php echo ($grandtotal - $grandexp); ?> 

								</td>
							</tr>
							</tbody>
						</table>			
				</div>	
		</div>	
				</div>
	</div>
</div>

			
<?php include 'dfooter.php'; ?>