<?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>
</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 - Balance Sheet Report
					<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>Description</th>
								<th>Data</th>
							</tr>
						</thead>
						<tbody>
						<?php $supertotal = 0; ?>
			            <!-- All Customers Liabilities -->
			            <?php 

							$allS = "SELECT * FROM customer_setup";
							$allSr = $db->query($allS);
							$allCustomerTotal = 0;
							$grand = 0;
							while($suppN = $allSr->fetchArray()){
							$preBal = 0;
							$open = "SELECT * FROM customer_setup ";
							$open .= "WHERE id = '".$suppN['id']."' ";
							$open_run = $db->query($open);
							while($open_row = $open_run->fetchArray()){
								$preBal += $open_row['open_balance'];
							}


							$sql = "SELECT DISTINCT invoiceNo FROM customerinvoice ";
							$sql .= "WHERE name = 'c".$suppN['id']."' ";
							//$sql .= "AND (date BETWEEN '".$_GET['sdate']."' AND '".$_GET['edate']."') ";
							$sql .= "AND payment_type != 'Opening Balance' ";
							$sql .= "ORDER BY id";
							
							$run = $db->query($sql);
							while($row = $run->fetchArray()){
								$rem = "SELECT * FROM customerinvoice ";
								$rem .= "WHERE invoiceNo = '".$row['invoiceNo']."' ";
								$rem_run = $db->query($rem);
								$rem_row = $rem_run->fetchArray();

								if ($rem_row['sale_type'] == 'Sale') {
									$preBal += $rem_row['remaining'];
								}elseif ($rem_row['sale_type'] == 'Purchase') {
									$preBal -= $rem_row['remaining'];
								}elseif ($rem_row['sale_type'] == 'Return') {
									$preBal -= $rem_row['remaining'];
								}
							}
							
						
							$sql2 = "SELECT * FROM payments ";
				            $sql2 .= "WHERE cs_id = 'c".$suppN['id']."' AND action = '1' ";
				            $sql2 .= "ORDER BY id";
				            $run2 = $db->query($sql2);
				            while($row2 = $run2->fetchArray()){
				            	if ($row2['debcre'] == 1) {
									$preBal -= $row2['amount'];
								}elseif ($row2['debcre'] == 2) {
									$preBal += $row2['amount'];
								}
							
							}

								//$supertotal += $preBal;
								$allCustomerTotal += $preBal;
						}
								$supertotal += $allCustomerTotal;
							?>
			             <tr>
							<td align="left"><h1>Customers Liabilities</h1></td>
							<td align="left">--</td>
						</tr>
			            <tr>
			            	<td>Total Customers Liabilities</td>
			            	<td><?php echo $allCustomerTotal; ?></td>
			            </tr>
			            <?php 
							$allS = "SELECT * FROM supplier_setup";
							$allSr = $db->query($allS);
							$allSupllierTotal = 0;
							$grand = 0;
							while($suppN = $allSr->fetchArray()){
							$preBal = 0;
							$open = "SELECT * FROM supplier_setup ";
							$open .= "WHERE id = '".$suppN['id']."' ";
							$open_run = $db->query($open);
							while($open_row = $open_run->fetchArray()){
								$preBal += $open_row['open_balance'];
							}


							$sql = "SELECT DISTINCT invoiceNo FROM customerinvoice ";
							$sql .= "WHERE name = 's".$suppN['id']."' ";
							//$sql .= "AND (date BETWEEN '".$_GET['sdate']."' AND '".$_GET['edate']."') ";
							$sql .= "AND payment_type != 'Opening Balance' ";
							$sql .= "ORDER BY id";
							
							$run = $db->query($sql);
							while($row = $run->fetchArray()){
								$rem = "SELECT * FROM customerinvoice ";
								$rem .= "WHERE invoiceNo = '".$row['invoiceNo']."' ";
								$rem_run = $db->query($rem);
								$rem_row = $rem_run->fetchArray();

								if ($rem_row['sale_type'] == 'Sale') {
									$preBal -= $rem_row['remaining'];
								}elseif ($rem_row['sale_type'] == 'Purchase') {
									$preBal += $rem_row['remaining'];
								}elseif ($rem_row['sale_type'] == 'Return') {
									$preBal -= $rem_row['remaining'];
								}
							}
							
							$sql2 = "SELECT * FROM payments2 ";
				            $sql2 .= "WHERE cs_id = 's".$suppN['id']."' AND action = '1' ";
				            $sql2 .= "ORDER BY id";
				            $run2 = $db->query($sql2);
				            while($row2 = $run2->fetchArray()){
				            	if ($row2['debcre'] == 1) {
									$preBal -= $row2['amount'];
								}elseif ($row2['debcre'] == 2) {
									$preBal += $row2['amount'];
								}
							}
							
								//$supertotal -= $preBal;
								$allSupllierTotal += $preBal;
					}
								$supertotal -= $allSupllierTotal;
							?>
			            <tr>
							<td align="left"><h1>Supplier Liabilities</h1></td>
							<td align="left">--</td>
						</tr>
			            <tr>
			            	<td>Total Supplier Liabilities</td>
			            	<td><?php echo $allSupllierTotal; ?></td>
			            </tr>

			            <?php
			         

			            	$other_title = $db->query("SELECT * FROM account_title WHERE name != 'Opening Balance' AND name != 'Maal Khareed Cash'");
								while ($fother_title = $other_title->fetchArray()) {

								//$grandtotal = 0;
								$sql = "SELECT * FROM other_accounts WHERE title = '".$fother_title['name']."' ";
								$run = $db->query($sql);

		            			$payment = 0;
								$receive = 0;
		            			while($row = $run->fetchArray()){
									$receive += $row['receive'];
			            			$payment += $row['payment'];
		            			}
		            		@$grandreceive += $receive;
		            		@$grandpayments += $payment;
		            	}
		            		@$supertotal -= $grandreceive;
		            		@$supertotal += $grandpayments;
			            ?>

			              <tr>
							<td align="left"><h1>Other Accounts</h1></td>
							<td align="left">--</td>
						</tr>
			            <tr>
			            	<td>Total Receive</td>
			            	<td><?php echo @$grandreceive; ?> Dainai Hain</td>
			            </tr>		
			            <tr>
			            	<td>Total Paid</td>
			            	<td><?php echo @$grandpayments; ?> Laynai Hain</td>
			            </tr>

			            <?php
			            		$bankgrand = 0;
			            		$totalbankgrand = 0;
			            		$other_title = $db->query("SELECT * FROM bank");
								while ($fother_title = $other_title->fetchArray()) {

								$bankgrand = $fother_title['balance'];
								$sql = "SELECT * FROM payments WHERE bank = '".$fother_title['name']."' AND bank != '' ";
								$run = $db->query($sql);

		            			
		            			while($row = $run->fetchArray()){
		            				if ($row['debcre'] == 1) {
		            					$bankgrand += $row['amount'];
		            				}else{
		            					$bankgrand -= $row['amount'];
		            				}
		            			}

		            			$sql = "SELECT * FROM payments2 WHERE bank = '".$fother_title['name']."' AND bank != '' ";
								$run = $db->query($sql);

		            			while($row = $run->fetchArray()){
		            				if ($row['debcre'] == 1) {
		            					$bankgrand -= $row['amount'];
		            				}else{
		            					$bankgrand += $row['amount'];
		            				}
		            			}

		            			$sql = "SELECT * FROM other_accounts WHERE bank = '".$fother_title['name']."' AND type = 2 ";
								$run = $db->query($sql);

		            			while($row = $run->fetchArray()){
		            				if ($row['receive'] > 0) {
		            					$bankgrand += $row['receive'];
		            				}else{
		            					$bankgrand -= $row['payment'];
		            				}
		            			}

		            			$sql = "SELECT * FROM expenses WHERE bank = '".$fother_title['name']."' AND type = 2 ";
								$run = $db->query($sql);

		            			while($row = $run->fetchArray()){
		            				if ($row['receive'] > 0) {
		            					$bankgrand += $row['receive'];
		            				}else{
		            					$bankgrand -= $row['payment'];
		            				}
		            			}

		            			$sql = "SELECT * FROM bank_accounts WHERE bank = '".$fother_title['name']."' ";
								$run = $db->query($sql);

		            			while($row = $run->fetchArray()){
		            				if ($row['receive'] > 0) {
		            					$bankgrand += $row['receive'];
		            				}else{
		            					$bankgrand -= $row['payment'];
		            				}
		            			}

		            		$totalbankgrand	+= $bankgrand;
		            	}

		            		$supertotal += $totalbankgrand;
			            ?>
			             <tr>
							<td align="left"><h1>Banks Accounts</h1></td>
							<td align="left">--</td>
						</tr>	
						<tr>
			            	<td>Total Bank Amount</td>
			            	<td><?php echo $totalbankgrand; ?></td>
			            </tr>



			            <?php
							// Start date
							$date = "2016/10/31";
							
			                // End date
							$sedate = date("Y/m/d");
							//$secus_date = explode('/', $sedate);
							//echo $seget_sdate = $secus_date[2].'/'.$secus_date[1].'/'.$secus_date[0];
			                $end_date = date ("Y/m/d", strtotime("-0 day", strtotime($sedate)));
							
							//$endDate = strtotime($_GET['sdate'] . "-1 day");
							//$endDate = strftime("%Y/%m/%d",$endDate);
							//$amount = 0;
							//$remaining = 0;
							$totalCash = 0;
							while (strtotime($date) <= strtotime($end_date)) {
							//echo "$date\n";
							$date = date ("Y/m/d", strtotime("+1 day", strtotime($date)));

							$sql = "SELECT DISTINCT invoiceNo FROM customerinvoice ";
							$sql .= "WHERE php_date = '".$date."' AND payment_type != 'Opening Balance' AND payment_type = 'Cash' ";
							$sql .= "ORDER BY date, id";
							
							$run = $db->query($sql);
							while($row = $run->fetchArray()){
								$rem = "SELECT * FROM customerinvoice ";
								$rem .= "WHERE invoiceNo = '".$row['invoiceNo']."' ";
								$rem_run = $db->query($rem);
								$rem_row = $rem_run->fetchArray();

								if ($rem_row['sale_type'] == 'Sale') {
									$totalCash += $rem_row['grand_total'];
								}elseif ($rem_row['sale_type'] == 'Return') {
									$totalCash -= $rem_row['grand_total'];
								}elseif ($rem_row['sale_type'] == 'Purchase') {
									$totalCash -= $rem_row['grand_total'];
								}
								
						}

				            $sql2 = "SELECT * FROM payments ";
				            $sql2 .= "WHERE action = '1' AND type = '1' ";
				            $sql2 .= "AND php_date = '".$date."' ";
				            $sql2 .= "ORDER BY php_date, id";
				            $run2 = $db->query($sql2);
				            while($row2 = $run2->fetchArray()){
				            	if ($row2['debcre'] == 1) {
									$totalCash += $row2['amount'];
								}elseif ($row2['debcre'] == 2) {
									$totalCash -= $row2['amount'];
								}
							}

				            $sql2 = "SELECT * FROM payments2 ";
				            $sql2 .= "WHERE action = '1' AND type = '1' ";
				            $sql2 .= "AND php_date = '".$date."' ";
				            $sql2 .= "ORDER BY php_date, id";
				            $run2 = $db->query($sql2);
				            while($row2 = $run2->fetchArray()){
				            	if ($row2['debcre'] == 1) {
									$totalCash -= $row2['amount'];
								}elseif ($row2['debcre'] == 2) {
									$totalCash += $row2['amount'];
								}
							
				           }   

							$sql2 = "SELECT * FROM expenses ";
				            $sql2 .= "WHERE php_date = '".$date."' AND type = 1 ";
				            $sql2 .= "ORDER BY php_date, id";
				            $run2 = $db->query($sql2);
				            while($row2 = $run2->fetchArray()){
				            	$totalCash -= $row2['payment'];
				            	$totalCash += $row2['receive'];
				            }

				           
				            $sql2 = "SELECT * FROM other_accounts ";
				            $sql2 .= "WHERE php_date = '".$date."' AND type = 1 ";
				            $sql2 .= "ORDER BY php_date, id";
				            $run2 = $db->query($sql2);
				            while($row2 = $run2->fetchArray()){
				            	if ($row2['receive'] > 0) {
				            		$totalCash += $row2['receive'];
				            	}else{
				            		$totalCash -= $row2['payment'];
				            	}
							}

							$sql2 = "SELECT * FROM bank_accounts ";
				            $sql2 .= "WHERE php_date = '".$date."' AND type = 1 ";
				            $sql2 .= "ORDER BY php_date, id";
				            $run2 = $db->query($sql2);
				            while($row2 = $run2->fetchArray()){
				            	if ($row2['receive'] > 0) {
				            		$totalCash -= $row2['receive'];
				            	}else{
				            		$totalCash += $row2['payment'];
				            	}
							}

						} // End of Opening
							//$preBal = $remaining - $amount;
						$supertotal += $totalCash;
						?>


			            <tr>
							<td align="left"><h1>Cash In Hand</h1></td>
							<td align="left">--</td>
						</tr>	
						<tr>
			            	<td>Total Cash In Hand Amount</td>
			            	<td><?php echo $totalCash; ?></td>
			            </tr>


			            <?php
			            	$totalstockgrand = 0;
			            	$other_title = $db->query("SELECT * FROM itemsetup");
							while ($fother_title = $other_title->fetchArray()) {
								$totalstockgrand += $fother_title['stock']*$fother_title['purchase_price'];
		            		} 
		            		$supertotal += $totalstockgrand;
		            	?>
			             <tr>
							<td align="left"><h1>Stock Data</h1></td>
							<td align="left">--</td>
						</tr>	
						<tr>
			            	<td>Total Stock Amount</td>
			            	<td><?php echo $totalstockgrand; ?></td>
			            </tr>	

			            <tr>
			            	<td></td>
			            	<td></td>
			            </tr>
			            <tr>
			            	<td></td>
			            	<td></td>
			            </tr>
			            <tr <?php echo ($supertotal >= 0) ?  'style="background: #7eda5e;"' : 'style="background:red;"'; ?>>
			            	<td>Your Balance Sheet</td>
			            	<td><?php echo $supertotal; ?></td>
			            </tr>	
							</tbody>
						</table>			
				</div>	
		</div>	
				</div>
	</div>
</div>

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