<?php
	include("includes/dbconfig.php");
	$pagetitle = "GL Transactions View";
	require('includes/applicationtop.php');
	require('includes/applicationmenu.php');
	
	$err= "";
	
	$startdatevalid = false;
	
	$transactionscount = 0;

	if(isset($_POST['gettrxnsbtn']))
	{	
		if($_SESSION["connectedusergroup"] == 1)
		{
			if($_POST['glaccount'] == "")
			{
				$err = $err."General Ledger Account Required!<br>";
			}
			
			if(trim($_POST['from'])=="")
			{
				$err = $err."Start Date Required!<br>";
			}
			else
			{
				$month = substr($_POST['from'], 0, 2);
				$day = substr($_POST['from'], 3, 2);
				$year = substr($_POST['from'], 6, 4);
				
				if(!checkdate($month, $day, $year))
				{
					$err = $err."Valid Start Date Required.<br>";
				}
				else
				{
					if(date("Y-m-d", strtotime(trim($_POST['from']))) > date("Y-m-d"))
					{
						$err = $err."Future Start Date Not Supported.<br>";
					}
					else
					{
						$startdatevalid = true;
					}
				}
			}
			
			if(trim($_POST['to'])=="")
			{
				$err = $err."End Date Required!<br>";
			}
			else
			{
				$month = substr($_POST['to'], 0, 2);
				$day = substr($_POST['to'], 3, 2);
				$year = substr($_POST['to'], 6, 4);
				
				if(!checkdate($month, $day, $year))
				{
					$err = $err."Valid End Date Required.<br>";
				}
				else
				{
					if(date("Y-m-d", strtotime(trim($_POST['to']))) > date("Y-m-d"))
					{
						$err = $err."Future End Date Not Supported.<br>";
					}
					else
					{
						if($startdatevalid)
						{
							if(date("Y-m-d", strtotime(trim($_POST['from']))) > date("Y-m-d", strtotime(trim($_POST['to']))))
							{
								$err = $err."Start Date Can Not Be Greater Than The End Date.<br>";
							}
							else
							{
								if($_POST['trxntag'] == "")
								{
									$glpostedtrxnsdata = DB::query("SELECT gl_gltrxn.value_date, gl_transaction_type.name, gl_gltrxn.cheque_number, gl_gltrxn.trxn_desc, IF(gl_gltrxn.trxn_amount > 0, gl_gltrxn.trxn_amount, 0.00) AS debit, IF(gl_gltrxn.trxn_amount < 0, (gl_gltrxn.trxn_amount * -1), 0.00) AS credit, gl_gltrxn.trxn_amount FROM gl_gltrxn, gl_transaction_type WHERE gl_gltrxn.chartmaster=%i AND gl_gltrxn.trxn_type = gl_transaction_type.id AND gl_gltrxn.value_date BETWEEN %s AND %s ORDER BY gl_gltrxn.value_date, gl_gltrxn.id", $_POST['glaccount'], date("Y-m-d H:i:s", strtotime(trim($_POST['from'] . " 00:00:00"))), date("Y-m-d H:i:s", strtotime(trim($_POST['to'] . " 23:59:00"))));
									
									$transactionscount = DB::count();
									
									if($transactionscount == 0)
										$err = $err."No Transactions Found In The Selected Date Range!<br>";
								}
								else
								{
									$glpostedtrxnsdata = DB::query("SELECT gl_gltrxn.value_date, gl_transaction_type.name, gl_gltrxn.cheque_number, gl_gltrxn.trxn_desc, IF(gl_gltrxn.trxn_amount > 0, gl_gltrxn.trxn_amount, 0.00) AS debit, IF(gl_gltrxn.trxn_amount < 0, (gl_gltrxn.trxn_amount * -1), 0.00) AS credit, gl_gltrxn.trxn_amount FROM gl_gltrxn, gl_transaction_type WHERE gl_gltrxn.chartmaster=%i AND gl_gltrxn.tag=%i AND gl_gltrxn.trxn_type = gl_transaction_type.id AND gl_gltrxn.value_date BETWEEN %s AND %s ORDER BY gl_gltrxn.value_date, gl_gltrxn.id", $_POST['glaccount'], $_POST['trxntag'], date("Y-m-d H:i:s", strtotime(trim($_POST['from'] . " 00:00:00"))), date("Y-m-d H:i:s", strtotime(trim($_POST['to'] . " 23:59:00"))));
									
									$transactionscount = DB::count();
									
									if($transactionscount == 0)
										$err = $err."No Transactions Found In The Selected Date Range!<br>";
								}
							}
						}
					}
				}
			}
		}
		else
		{
			$err = $err."Limited Access!<br>";
		}

	}
?>
			<!-- Content Wrapper. Contains page content -->
			<div class="content-wrapper">
				<!-- Content Header (Page header) -->
				<section class="content-header">
					<h1>GL Transactions View</h1>
					<ol class="breadcrumb">
					</ol>
				</section>

				<!-- Main content -->
				<section class="content">
					<!-- Default box -->
					<div class="box box-primary">
						<?php
							if($_SESSION["connectedusergroup"] == 1)
							{
						?>
						<div class="box-header with-border text-center">
							<h3 class="box-title">GL Transactions List</h3>
						</div>
						
						<div class="box-body">
							<?php
								if($err!="")
								{
							?>
							<div class="alert alert-danger alert-dismissable">
								<i class="glyphicon glyphicon-ban-circle"></i>
								<button type="button" class="close" data-dismiss="alert" aria-hidden="true">&times;</button><br>
								<?php 
									echo $err; 
								?>
							</div>
							<?php
								}
							?>
							
							<div id="servererrormessage">
							</div>
							
							<div class="box-body table-responsive no-padding">
								<div class="col-sm-12">
									<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
										<div class="form-group col-sm-4">
											<label for="glaccount">General Ledger Account</label>
											<select  class="form-control" name="glaccount" id="glaccount" data-placeholder="Choose One">
												<option value="">Choose One</option>
												<?php
													$glaccountsdata = DB::query("SELECT gl_chartmaster.id, CONCAT(gl_chartmaster.account_code, ' - ', gl_chartmaster.account_name, ' - ', gl_class.gl_class_name) AS gl_account FROM gl_chartmaster, gl_class WHERE gl_chartmaster.account_class = gl_class.id ORDER BY gl_class.id, gl_chartmaster.account_code, gl_chartmaster.account_name");
													
													foreach ($glaccountsdata as $glaccount)
													{
												?>
												<option <?php if(isset($_POST['glaccount']) && $glaccount['id']==$_POST['glaccount']) echo "selected=\"selected\""; ?> value="<?php echo $glaccount['id']; ?>"><?php echo $glaccount['gl_account']; ?></option>
												<?php
													}
												?>
											</select>
										</div><!-- form-group -->
										
										<div class="form-group col-sm-2">
											<label for="trxntag">Tag</label>
											<select  class="form-control" name="trxntag" id="trxntag" data-placeholder="Choose One">
												<option value="">Choose One</option>
												<?php
													$trxntagsdata = DB::query("SELECT id, tag_name FROM gl_tag");
													
													foreach ($trxntagsdata as $trxntag)
													{
												?>
												<option <?php if(isset($_POST['trxntag']) && $trxntag['id']==$_POST['trxntag']) echo "selected=\"selected\""; ?> value="<?php echo $trxntag['id']; ?>"><?php echo $trxntag['tag_name']; ?></option>
												<?php
													}
												?>
											</select>
										</div><!-- form-group -->
										
										<div class="form-group col-sm-3">
											<label for="from">Start Date</label>
											<input type="text" value="<?php echo isset($_POST['from']) ? $_POST['from'] : "" ?>" name="from" id="from" class="form-control" data-inputmask="'alias': 'mm/dd/yyyy'" data-mask />
										</div><!-- form-group -->
										
										<div class="form-group col-sm-3">
											<label for="to">End Date</label>
											<div class="input-group">
												<input type="text" value="<?php echo isset($_POST['to']) ? $_POST['to'] : "" ?>" name="to" id="to" class="form-control" data-inputmask="'alias': 'mm/dd/yyyy'" data-mask />
												<span class="input-group-btn">
													<button class="btn btn-primary btn-flat" type="submit" name="gettrxnsbtn" id="gettrxnsbtn">Go!</button>
												</span>
											</div>
										</div><!-- form-group -->
									</form>									
								</div>
								
								<div class="col-md-12" style="margin-top: 25px"></div>
								
								<table class="table table-hover">
									<thead>
										<tr>
											<th class="text-center" style="width: 12%">Value Date</th>
											<th style="width: 12%">Type</th>
											<th style="width: 25%">Narration</th>
											<th>Chq #</th>
											<th class="text-right">Debit</th>
											<th class="text-right">Credit</th>
											<th class="text-right">Balance</th>
										</tr>
									</thead>
									<tbody>
										<?php
											if($transactionscount > 0)
											{
												$usetag = ($_POST['trxntag'] > 0) ? $_POST['trxntag'] : 0;
												
												$accountbalance = DB::queryFirstField("SELECT SUM(trxn_amount) AS balancebd FROM gl_gltrxn WHERE chartmaster=%i AND tag=%i AND value_date<%s", $_POST['glaccount'], $usetag, date("Y-m-d", strtotime(trim($_POST['from']) . " 00:00:00")));
										?>
										<tr>
											<td class="text-center"><?php echo date("d-m-Y", strtotime(trim($_POST['from']))); ?></td>
											<td></td>
											<td>Balance b/d</td>
											<td></td>
											<td class="text-right"></td>
											<td class="text-right"></td>
											<td class="text-right"><?php echo ($accountbalance < 0) ? number_format(($accountbalance * -1), 2) . " Cr" : number_format($accountbalance, 2) . " Dr"; ?></td>
										</tr>
										<?php
												foreach($glpostedtrxnsdata as $glpostedtrxn)
												{
													$accountbalance += $glpostedtrxn['trxn_amount'];
										?>
										<tr>
											<td class="text-center"><?php echo date("d-m-Y", strtotime($glpostedtrxn['value_date'])); ?></td>
											<td><?php echo $glpostedtrxn['name']; ?></td>
											<td><?php echo $glpostedtrxn['trxn_desc']; ?></td>
											<td><?php echo $glpostedtrxn['cheque_number']; ?></td>
											<td class="text-right"><?php echo number_format($glpostedtrxn['debit'], 2); ?></td>
											<td class="text-right"><?php echo number_format($glpostedtrxn['credit'], 2); ?></td>
											<td class="text-right"><?php echo ($accountbalance < 0) ? number_format(($accountbalance * -1), 2) . " Cr" : number_format($accountbalance, 2) . " Dr"; ?></td>
										</tr>
										<?php
												}
											}
										?>
									</tbody>
								</table>
							</div><!-- /.box-body -->
						<?php
							}
							else
							{
						?>
						<div class="alert alert-danger alert-dismissable">
							<i class="glyphicon glyphicon-ban-circle"></i><br>
							Limited Access
						</div>
						<?php
							}
						?>
					</div><!-- /.box -->
				</section><!-- /.content -->
			</div><!-- /.content-wrapper -->

			<footer class="main-footer text-center">
				<strong>Copyright &copy; <?php echo date("Y"); ?> BSMPOS.</strong> All rights reserved.
			</footer>
		</div><!-- ./wrapper -->

		<!-- jQuery 2.1.3 -->
		<script src="plugins/jQuery/jQuery-2.1.3.min.js"></script>
		<!-- Bootstrap 3.3.2 JS -->
		<script src="bootstrap/js/bootstrap.min.js" type="text/javascript"></script>
		<!-- SlimScroll -->
		<script src="plugins/slimScroll/jquery.slimScroll.min.js" type="text/javascript"></script>
		<!-- InputMask -->
		<script src="plugins/input-mask/jquery.inputmask.js" type="text/javascript"></script>
		<script src="plugins/input-mask/jquery.inputmask.date.extensions.js" type="text/javascript"></script>
		<script src="plugins/input-mask/jquery.inputmask.extensions.js" type="text/javascript"></script>
		<!-- FastClick -->
		<script src='plugins/fastclick/fastclick.min.js'></script>
		<!-- AdminLTE App -->
		<script src="dist/js/app.min.js" type="text/javascript"></script>
		<script src="plugins/bert/storepos.js" type="text/javascript"></script>
		<script src="plugins/bert/jquery.price_format.min.js" type="text/javascript"></script>
		
		<script type="text/javascript">
			$(function() 
			{
				//Datemask2 mm/dd/yyyy
				$("#datemask2").inputmask("mm/dd/yyyy", {"placeholder": "mm/dd/yyyy"});
				//Money Euro
				$("[data-mask]").inputmask();
				
				$("#glaccount").focus();
			});
		</script>
	</body>
</html>