1#@!#!123s

��JFIF�� 1#@!#!123s
: / home / mfms22 / mbcsl.mfmsbd.com /
Filename : opening_closing.php
back
<?PHP
	require 'functions.php';
	checkLogin();
	checkPermissionReport();
	$db_link = connect();

	//Variables $year and $month provide the pre-set values for input fields
	$year = date("Y",time());
	$month = date("m",time());
?>

<?php headandmenu('Opening & Closing Balance', '11r'); ?>

<!--main content start-->
      <section id="main-content">
          <section class="wrapper" style="min-height: 850px;">


          <!--breadcrumbs start -->
          	<div class="row">
                  <div class="col-lg-12 pb-4">
                      <!--breadcrumbs start -->
                      <nav aria-label="breadcrumb">
                          <ol class="breadcrumb">
                              <li class="breadcrumb-item"><a href="start.php"><i class="fa fa-home"></i> Dashboard</a></li>
                              <li class="breadcrumb-item"><a href="#">Reports</a></li>
                              <li class="breadcrumb-item active" aria-current="page">Opening & Closing Balance</li>
                          </ol>
                      </nav>
                  </div>
              </div>
             <!--breadcrumbs end -->


		<!-- MENU: Selection Bar -->
		<div id="menu_selection">
			<form action="opening_closing.php" method="post">
				

                From: <input type="number" min="2006" max="2206" name="rep_year1" style="width:100px;" value="<?PHP if ($month == 01) echo $year-1; else echo $year; ?>" placeholder="Give Year" />
				<select name="rep_month_from">
					<option value="01" <?PHP if ($month == 2) echo 'selected="selected"' ?> >January</option>
					<option value="02" <?PHP if ($month == 3) echo 'selected="selected"' ?> >February</option>
					<option value="03" <?PHP if ($month == 4) echo 'selected="selected"' ?> >March</option>
					<option value="04" <?PHP if ($month == 5) echo 'selected="selected"' ?> >April</option>
					<option value="05" <?PHP if ($month == 6) echo 'selected="selected"' ?> >May</option>
					<option value="06" <?PHP if ($month == 7) echo 'selected="selected"' ?> >June</option>
					<option value="07" <?PHP if ($month == 8) echo 'selected="selected"' ?> >July</option>
					<option value="08" <?PHP if ($month == 9) echo 'selected="selected"' ?> >August</option>
					<option value="09" <?PHP if ($month == 10) echo 'selected="selected"' ?> >September</option>
					<option value="10" <?PHP if ($month == 11) echo 'selected="selected"' ?> >October</option>
					<option value="11" <?PHP if ($month == 12) echo 'selected="selected"' ?> >November</option>
					<option value="12" <?PHP if ($month == 1) echo 'selected="selected"' ?> >December</option>
				</select>
                 <input type="number" name="date1" placeholder="Day" required/>
				To: <input type="number" min="2006" max="2206" name="rep_year2" style="width:100px;" value="<?PHP if ($month == 01) echo $year-1; else echo $year; ?>" placeholder="Give Year" />
				<select name="rep_month_to">
					<option value="01" <?PHP if ($month == 2) echo 'selected="selected"' ?> >January</option>
					<option value="02" <?PHP if ($month == 3) echo 'selected="selected"' ?> >February</option>
					<option value="03" <?PHP if ($month == 4) echo 'selected="selected"' ?> >March</option>
					<option value="04" <?PHP if ($month == 5) echo 'selected="selected"' ?> >April</option>
					<option value="05" <?PHP if ($month == 6) echo 'selected="selected"' ?> >May</option>
					<option value="06" <?PHP if ($month == 7) echo 'selected="selected"' ?> >June</option>
					<option value="07" <?PHP if ($month == 8) echo 'selected="selected"' ?> >July</option>
					<option value="08" <?PHP if ($month == 9) echo 'selected="selected"' ?> >August</option>
					<option value="09" <?PHP if ($month == 10) echo 'selected="selected"' ?> >September</option>
					<option value="10" <?PHP if ($month == 11) echo 'selected="selected"' ?> >October</option>
					<option value="11" <?PHP if ($month == 12) echo 'selected="selected"' ?> >November</option>
					<option value="12" <?PHP if ($month == 1) echo 'selected="selected"' ?> >December</option>
				</select>
                <input type="number" name="date2" placeholder="Day" required/>
				<input type="submit" name="select" value="Balance" />
			</form>
		</div>

		<?PHP
		if(isset($_POST['select'])){

			//Sanitize user input
			$rep_from_date = sanitize($db_link, $_POST['date1']);
            $rep_to_date = sanitize($db_link, $_POST['date2']);
			$rep_month_from = sanitize($db_link, $_POST['rep_month_from']);
			$rep_month_to = sanitize($db_link, $_POST['rep_month_to']);
			$rep_year1 = sanitize($db_link, $_POST['rep_year1']);
			$rep_year2 = sanitize($db_link, $_POST['rep_year2']);

			//Calculate UNIX TIMESTAMP for first and last day of selected month
			$firstDay = mktime(0, 0, 0, $rep_month_from, $rep_from_date, $rep_year1);
			$lastDay = mktime(0, 0, 0, $rep_month_to, $rep_to_date, $rep_year2);

			//Make array for exporting data
			$_SESSION['rep_export'] = array();
			$_SESSION['rep_exp_title'] = $rep_from_date.'/'.$rep_month_from.'/'.$rep_year1.' to '.$rep_to_date.'/'.$rep_month_to.'/'.$rep_year2.'_Opening_Closing_Balance';

            // Installment Cpaital
                $sql_installment = "SELECT SUM(amount) as amount FROM capital WHERE cap_date BETWEEN $firstDay AND $lastDay";
                $query_installment = mysqli_query($db_link, $sql_installment);
                $row_installment = mysqli_fetch_assoc($query_installment);


            // sum all from incomes
                $sum_sql_income = "SELECT SUM(inc_amount) as inc_amount FROM incomes WHERE inc_date BETWEEN $firstDay AND $lastDay";
                $sum_query_income = mysqli_query($db_link, $sum_sql_income);
                $row_total_income = mysqli_fetch_assoc($sum_query_income);  
                

            // sub all from expense
                $sum_sql_expense = "SELECT SUM(exp_amount) as exp_amount FROM `expenses` WHERE exp_date BETWEEN $firstDay AND $lastDay";
                $sum_query_expense = mysqli_query($db_link, $sum_sql_expense);
                $row_total_expense = mysqli_fetch_assoc($sum_query_expense);   
                

			//Select bought and sold Shares from SHARES
			$sql_shares = "SELECT * FROM shares WHERE share_date BETWEEN $firstDay AND $lastDay";
			$query_shares = mysqli_query($db_link, $sql_shares);
			checkSQL($db_link, $query_shares);
			$total_share_buys = 0;
			$total_share_sales = 0;
			while($row_shares = mysqli_fetch_assoc($query_shares)){
				if($row_shares['share_amount'] >= 0){
					$total_share_buys = $total_share_buys + $row_shares['share_value'];
				}
				elseif($row_shares['share_amount'] < 0){
					$total_share_sales = $total_share_sales + $row_shares['share_value'] * (-1);
				}
			}


			//Select Saving Deposits from SAVINGS
			$sql_savdep = "SELECT * FROM savings WHERE sav_date BETWEEN $firstDay AND $lastDay AND savtype_id = 1";
			$query_savdep = mysqli_query($db_link, $sql_savdep);
			checkSQL($db_link, $query_savdep);
			$total_savdep = 0;
			while($row_savdep = mysqli_fetch_assoc($query_savdep)){
				$total_savdep = $total_savdep + $row_savdep['sav_amount'];
			}

            // Total dps balance
            $sql_dps_balance = "SELECT SUM(sav_amount) as sav_amount FROM dps WHERE sav_date BETWEEN $firstDay AND $lastDay AND savtype_id=10";
            $query_dps_balance = mysqli_query($db_link, $sql_dps_balance);
            $total_dps_balance = mysqli_fetch_assoc($query_dps_balance);


			//Select Loan Recoveries from LOANS
			$sql_loanrec = "SELECT * FROM ltrans WHERE ltrans_date BETWEEN $firstDay AND $lastDay";
			$query_loanrec = mysqli_query($db_link, $sql_loanrec);
			checkSQL($db_link, $query_loanrec);
			$total_loanrec = 0;
			while($row_loanrec = mysqli_fetch_assoc($query_loanrec)){
				$total_loanrec = $total_loanrec + $row_loanrec['ltrans_principal'];
			}


			//Select Saving Withdrawals from SAVINGS
			$sql_savwithd = "SELECT * FROM savings WHERE sav_date BETWEEN $firstDay AND $lastDay AND savtype_id = 2";
			$query_savwithd = mysqli_query($db_link, $sql_savwithd);
			checkSQL($db_link, $query_savwithd);
			$total_savwithd = 0;
			while($row_savwithd = mysqli_fetch_assoc($query_savwithd)){
				$total_savwithd = $total_savwithd + $row_savwithd['sav_amount'];
			}
			$total_savwithd = $total_savwithd * (-1);


            // Total DPS Withdrawals balance
            $sql_dpswith_balance = "SELECT SUM(sav_amount) as sav_amount FROM dps WHERE sav_date BETWEEN $firstDay AND $lastDay AND savtype_id=11";
            $query_dpswith_balance = mysqli_query($db_link, $sql_dpswith_balance);
            $total_dpswith_balance = mysqli_fetch_assoc($query_dpswith_balance);
            $total_dpswith_balance = $total_dpswith_balance['sav_amount'] * (-1);


			//Select Loans Out from LOANS
			$sql_loanout = "SELECT * FROM loans WHERE loan_dateout BETWEEN $firstDay AND $lastDay";
			$query_loanout = mysqli_query($db_link, $sql_loanout);
			checkSQL($db_link, $query_loanout);
			$total_loanout = 0;
			while($row_loanout = mysqli_fetch_assoc($query_loanout)){
				$total_loanout = $total_loanout + $row_loanout['loan_principal'];

			}


                // Total Capital Additions & Deductions
                $total_capital_additions =$total_share_buys + $total_savdep + $total_loanrec+$row_total_income['inc_amount']+$row_installment['amount']+$total_dps_balance['sav_amount'];

                $total_capital_deductions = $total_share_sales + $total_loanout + $total_savwithd+$row_total_expense['exp_amount']+$total_dpswith_balance;

                $closing_blance = number_format($total_capital_additions - $total_capital_deductions);
           

			//Prepare data for export to Excel file
            array_push($_SESSION['rep_export'], array("Type" => "Installment Capital", "Amount" => $row_installment['amount']));
            array_push($_SESSION['rep_export'], array("Type" => "Installment Capital", "Amount" => $row_total_income['inc_amount']));
			array_push($_SESSION['rep_export'], array("Type" => "Shares Out", "Amount" => $total_share_buys));
			array_push($_SESSION['rep_export'], array("Type" => "Saving Deposits", "Amount" => $total_savdep));
            array_push($_SESSION['rep_export'], array("Type" => "DPS", "Amount" => $total_dps_balance['sav_amount']));
			array_push($_SESSION['rep_export'], array("Type" => "Loan Recoveries", "Amount" => $total_loanrec));

			array_push($_SESSION['rep_export'], array("Type" => "Total Additions", "Amount" => $total_loanrec+$total_savdep+$total_dps_balance['sav_amount']+$total_share_buys+$row_installment['amount']+$row_total_income['inc_amount']));

			array_push($_SESSION['rep_export'], array("Type" => "", "Amount" => ""));
            array_push($_SESSION['rep_export'], array("Type" => "Total Expense", "Amount" => $row_total_expense['exp_amount']));
			array_push($_SESSION['rep_export'], array("Type" => "Shares In", "Amount" => $total_share_sales));
			array_push($_SESSION['rep_export'], array("Type" => "Saving Withdrawals", "Amount" => $total_savwithd+$total_dpswith_balance));
            array_push($_SESSION['rep_export'], array("Type" => "DPS Withdrawals", "Amount" => $total_dpswith_balance));
			array_push($_SESSION['rep_export'], array("Type" => "Loans Out", "Amount" => $total_loanout));

			array_push($_SESSION['rep_export'], array("Type" => "Total Deductions", "Amount" => $total_share_sales+$total_loanout+$total_savwithd+$total_dpswith_balance+$row_total_expense['exp_amount']));

            array_push($_SESSION['rep_export'], array("Type" => "", "Amount" => ""));
            array_push($_SESSION['rep_export'], array("Type" => "", "Amount" => ""));
            array_push($_SESSION['rep_export'], array("Type" => "Closing Balance", "Amount" => "$closing_blance"));


            

			?>

			<!-- Export Button -->
			<form class="export" action="rep_export.php" method="post">
				<input type="submit" name="export_rep" value="Export Report" />
			</form>

			<!-- TABLE 1: Capital Additions -->
			<table id="tb_table" style="width:70%">
				<colgroup>
					<col width="50%"/>
					<col width="50%"/>
				</colgroup>
				<tr>
					<th class="title" colspan="2">Capital Additions for <?PHP echo $rep_from_date.'/'.$rep_month_from.'/'.$rep_year1.' to '.$rep_to_date.'/'.$rep_month_to.'/'.$rep_year2; ?></th>
				</tr>
				<tr>
					<th>Type</th>
					<th>Amount</th>
				</tr>
                <tr>
					<td>Capital</td>
					<td><?= number_format($row_installment['amount']).' '.$_SESSION['set_cur']; ?></td>
				</tr>
                <tr>
					<td>Total Incomes</td>
					<td><?= number_format($row_total_income['inc_amount']).' '.$_SESSION['set_cur'] ?></td>
				</tr>

				<tr>
					<td>Shares Out</td>
					<td><?PHP echo number_format($total_share_buys).' '.$_SESSION['set_cur'] ?></td>
				</tr>
				<tr class="alt">
					<td>Saving Deposits</td>
					<td><?PHP 
                        echo number_format($total_savdep).' '.$_SESSION['set_cur'] 
                    ?></td>
				</tr>
                <tr class="alt">
					<td>DPS</td>
					<td><?PHP 
                        echo number_format($total_dps_balance['sav_amount']).' '.$_SESSION['set_cur'] 
                    ?></td>
				</tr>
				<tr>
					<td>Loan Recoveries</td>
					<td><?PHP echo number_format($total_loanrec).' '.$_SESSION['set_cur'] ?></td>
				</tr>
				<tr class="balance">
					<td>Total Capital Additions:</td>
					<td><?PHP echo  number_format($total_capital_additions).' '.$_SESSION['set_cur'] ?></td>
				</tr>
			</table>

			<!-- TABLE 2: Capital Deductions -->
			<table id="tb_table" style="width:70%">
				<colgroup>
					<col width="50%"/>
					<col width="50%"/>
				</colgroup>
				<tr>
					<th class="title" colspan="2">Capital Deductions for <?PHP echo $rep_from_date.'/'.$rep_month_from.'/'.$rep_year1.' to '.$rep_to_date.'/'.$rep_month_to.'/'.$rep_year2; ?></th>
				</tr>
				<tr>
					<th>Type</th>
					<th>Amount</th>
				</tr>
                <tr>
					<td>Total Expense</td>
					<td><?= number_format($row_total_expense['exp_amount']).' '.$_SESSION['set_cur']; ?></td>
				</tr>
				<tr>
					<td>Shares In</td>
					<td><?PHP echo number_format($total_share_sales).' '.$_SESSION['set_cur'] ?></td>
				</tr>
				<tr class="alt">
					<td>Loans Out</td>
					<td><?PHP echo number_format($total_loanout).' '.$_SESSION['set_cur'] ?></td>
				</tr>
				<tr>
					<td>Saving Withdrawals</td>
					<td><?PHP echo number_format($total_savwithd).' '.$_SESSION['set_cur'] ?></td>
				</tr>
                <tr>
					<td>DPS Withdrawals</td>
					<td><?PHP echo number_format($total_dpswith_balance).' '.$_SESSION['set_cur'] ?></td>
				</tr>
				<tr class="balance">
					<td>Total Capital Deductions:</td>
					<td><?PHP echo number_format($total_capital_deductions).' '.$_SESSION['set_cur'] ?></td>
				</tr>
			</table>



            <!-- Opening and Closing -->

            <table id="tb_table" style="width:70%"> 
				
                <tr>
					<th class="title" colspan="2">Closing Balance: <?PHP echo number_format($total_capital_additions-$total_capital_deductions).' '.$_SESSION['set_cur']; ?></th>
				</tr>
            </table>

            		

		<?PHP } ?>




          </section>
      </section>
      <!--main content end-->
      
   <?php footer(); ?>