<?php 
include("../../config.php");
$start_date = $_POST['start_date'];
$end_date 	= $_POST['end_date'];
$search_term= $_POST['search_term'];

if($search_term == ''){
	$query = mysqli_query($conn, "SELECT * FROM `act_master`");	
}else{
	$query = mysqli_query($conn, "SELECT * FROM `act_master` WHERE id = '".$search_term."'");
}

$data = array();
while ($row = mysqli_fetch_assoc($query)) {
	$data[] = $row;
}

$table = '';
foreach ($data as $key => $datas) {
	$opening_dr_amount = $datas['dr_open_bal'];
	$opening_cr_amount = $datas['cr_open_bal'];


	$debit_op = mysqli_query($conn, "SELECT abs(sum(amount)) AS debit_amount_op FROM `general_ledger` WHERE `debit_account` = '".$datas['act_code']."' AND `voucher_date` < '".risformatDate($start_date,'Y-m-d')."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'");
	$debit_op_row = mysqli_fetch_assoc($debit_op);

	$credit_op = mysqli_query($conn, "SELECT abs(sum(amount)) AS credit_amount_op FROM `general_ledger` WHERE `credit_account` = '".$datas['act_code']."' AND `voucher_date` < '".risformatDate($start_date,'Y-m-d')."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'");
	$credit_op_row = mysqli_fetch_assoc($credit_op);

	$debit = mysqli_query($conn, "SELECT abs(sum(amount)) AS debit_amount FROM `general_ledger` WHERE `debit_account` = '".$datas['act_code']."' AND `voucher_date` >= '".risformatDate($start_date,'Y-m-d')."' AND `voucher_date` <= '".risformatDate($end_date,'Y-m-d')."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'");
	$debit_row = mysqli_fetch_assoc($debit);

	$credit = mysqli_query($conn, "SELECT abs(sum(amount)) AS credit_amount_op FROM `general_ledger` WHERE `credit_account` = '".$datas['act_code']."' AND `voucher_date` >= '".risformatDate($start_date,'Y-m-d')."' AND `voucher_date` <= '".risformatDate($end_date,'Y-m-d')."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'");
	$credit_row = mysqli_fetch_assoc($credit);

	$closing = ($opening_dr_amount + $debit_op_row['debit_amount_op'] + $debit_row['debit_amount']) - ($opening_cr_amount + $credit_op_row['credit_amount_op'] + $credit_row['credit_amount_op']);

	if($closing >= 0){
		$flag = 'Dr';
	}else{
		$flag = 'Cr';
	}
	$table .='
	<tr>
		<td><a href="#" id='.$datas["act_code"].' onclick="LedgerAccountDetail(this);">'.$datas["act_code"].'</a></td>
		<td>'.$datas['ac_name'].'</td>
		<td style="text-align:right">'.customFormatMoney(abs($closing)).'</td>
		<td style="text-align:center">'.$flag.'</td>
	</tr>
	';
}

$data = array('tbody' => $table);
echo json_encode($data);
die();
?>