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

if($report_type == 'ACCOUNT'){
	$master_account = combine(getAllRecords('act_master'), 'act_code', 'ac_name');
	$table          = '';

	$select = mysqli_query($conn, "SELECT * FROM `act_master` WHERE act_code = '".$account_code."'");
	$rows = mysqli_fetch_assoc($select);

	$opening_dr_amount = $rows['dr_open_bal'];
	$opening_cr_amount = $rows['cr_open_bal'];

	$debit_op = mysqli_query($conn, "SELECT abs(sum(amount)) AS debit_amount_op FROM `general_ledger` WHERE `debit_account` = '".$account_code."' AND `voucher_date` < '".risformatDate($start_date,'Y-m-d')."'");
	$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` = '".$account_code."' AND `voucher_date` < '".risformatDate($start_date,'Y-m-d')."'");
	$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` = '".$account_code."' AND `voucher_date` >= '".risformatDate($start_date,'Y-m-d')."' AND `voucher_date` <= '".risformatDate($end_date,'Y-m-d')."'");
	$debit_row = mysqli_fetch_assoc($debit);

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

	$opening = ($opening_dr_amount + $debit_op_row['debit_amount_op']) - ($opening_cr_amount + $credit_op_row['credit_amount_op']);

	$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($opening >= 0){
		$op_flag      	= 'Dr';
	}else{
		$op_flag      	= 'Cr';
	}

	if($closing >= 0){
		$cl_flag = 'Dr';
	}else{
		$cl_flag = 'Cr';
	}

	$query = mysqli_query($conn,"SELECT * FROM `general_ledger` WHERE (`credit_account` = '".$account_code."' OR `debit_account` = '".$account_code."') AND `amount` != 0 AND `voucher_date` >= '".risformatDate($start_date,'Y-m-d')."' AND `voucher_date` <= '".risformatDate($end_date,'Y-m-d')."' AND flag != 'G' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."' ORDER BY voucher_date,voucher_no,id");


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

	if($opening >= 0){
	$table.='
	<tr style="font-weight:bolder">
		<td></td>
		<td></td>
		<td></td>
		<td>Opeing</td>
		<td style="text-align:right">'.customFormatMoney(abs($opening)).'</td>
		<td style="text-align:right">0.00</td>
		<td style="text-align:right">'.customFormatMoney(abs($opening)).'</td>
		<td style="text-align:center">Dr</td>
		<td style="text-align:center"></td>
		<td style="text-align:center"></td>
		<td style="text-align:center"></td>
		<td style="text-align:center"></td>
	</tr>
	';
	}

	if($opening < 0){
		$table.='
		<tr style="font-weight:bolder">
			<td></td>
			<td></td>
			<td></td>
			<td>Opeing</td>
			<td style="text-align:right">0.00</td>
			<td style="text-align:right">'.customFormatMoney(abs($opening)).'</td>
			<td style="text-align:right">'.customFormatMoney(abs($opening)).'</td>
			<td style="text-align:center">Cr</td>
			<td style="text-align:center"></td>
			<td style="text-align:center"></td>
			<td style="text-align:center"></td>
			<td style="text-align:center"></td>
		</tr>';
	}

	$tclosing 		 = $opening;
	$total_camount 	 = 0;
	$total_damount 	 = 0;
	$total1_camount  = 0;
	$total1_damount  = 0;
	foreach ($data as $key => $datas) {
        if($datas['flag'] == 'F'){
			$type = 'Credit Note';
		}

		if($datas['flag'] == 'E'){
			$type = 'Debit Note';
		}

		if($datas['flag'] == 'G' && $datas['flag'] == 'S'){
			$type = 'Sales';
		}

		if($datas['flag'] == 'G' && $datas['flag'] == 'P'){
			$type = 'Purchase';
		}

		if($datas['flag'] == 'P'){
			$type = 'Purchase';
		}

		if($datas['flag'] == 'S'){
			$type = 'Sales';
		}

		if($datas['flag'] == 'J'){
			$type = 'Journal Voucher';
		}

		if($datas['flag'] == 'T'){
			$type = 'TDS';
		}

		if($datas['flag'] == 'B'){
			$code = explode('-', $datas['voucher_no']);
			if($code[0] == 'BP'){
				$type = 'Bank Payment';		
			}else{
				$type = 'Bank Receipt';
			}
		}

		if($datas['flag'] == 'C'){
			$code = explode('-', $datas['voucher_no']);
			if($code[0] == 'CP'){
				$type = 'Cash Payment';		
			}else{
				$type = 'Cash Receipt';
			}
		}

		$camount = 0;
		$damount = 0;
		if($account_code == $datas['credit_account']){
			$account_name = @$master_account[$datas['debit_account']];
			if($datas['flag'] == 'B' || $datas['flag'] == 'C' || $datas['flag'] == 'J' || $datas['flag'] == 'T'){
				$camount        = $datas['amount'];	
				$total_camount  += $datas['amount'];	
				$total1_camount += $datas['amount'];	
			}else{
				$camount        = $datas['net_amount'];
				$total_camount  += $datas['net_amount'];
				$total1_camount += $datas['net_amount'];
			}
			
		}

		if($account_code == $datas['debit_account']){
			$account_name = @$master_account[$datas['credit_account']];
			if($datas['flag'] == 'B' || $datas['flag'] == 'C' || $datas['flag'] == 'J' || $datas['flag'] == 'T'){
				$damount       = $datas['amount'];	
				$total_damount += $datas['amount'];	
				$total1_damount += $datas['amount'];	
			}else{
				$damount        = $datas['net_amount'];
				$total_damount  += $datas['net_amount'];	
				$total1_damount += $datas['net_amount'];	
			}
		}

		$tclosing = ($tclosing + $damount) -  $camount;

		if($tclosing >= 0){
			$bflag = 'Dr';
		}else{
			$bflag = 'Cr';
		}
		$table.='
		<tr>
			<td>'.risFormatDate($datas['voucher_date'],'d/m/Y').'</td>
			<td>'.$datas['voucher_no'].'</td>
			<td>'.$type.'</td>
			<td>'.$account_name.'</td>
			<td style="text-align:right">'.customFormatMoney($damount).'</td>
			<td style="text-align:right">'.customFormatMoney($camount).'</td>
			<td style="text-align:right">'.customFormatMoney(abs($tclosing)).'</td>
			<td style="text-align:center">'.$bflag.'</td>
			<td style="text-align:left">'.$datas['remark'].'</td>
			<td style="text-align:center"><input type="checkbox" class="height10"></td>
			<td style="text-align:left"></td>
			<td style="text-align:left">

			<input type="hidden" name="data['.$key.'][voucher_date]" value="'.risFormatDate($datas['voucher_date'],'Y-m-d').'" class="form-control">

			<input type="hidden" name="data['.$key.'][voucher_no]" value="'.$datas['voucher_no'].'" class="form-control">

			<input type="hidden" name="data['.$key.'][type]" value="'.$type.'" class="form-control">

			<input type="hidden" name="data['.$key.'][account_name]" value="'.$account_name.'" class="form-control">

			<input type="hidden" name="data['.$key.'][damount]" value="'.$damount.'" class="form-control">

			<input type="hidden" name="data['.$key.'][camount]" value="'.$camount.'" class="form-control">

			<input type="hidden" name="data['.$key.'][tclosing]" value="'.$tclosing.'" class="form-control">

			<input type="hidden" name="data['.$key.'][bflag]" value="'.$bflag.'" class="form-control">

			<input type="hidden" name="data['.$key.'][remark]" value="'.$datas['remark'].'" class="form-control">
			</td>


		</tr>
		';
	}

	$table.='
		<tr style="font-weight:bolder">
			<td></td>
			<td></td>
			<td></td>
			<td>Transaction Balance</td>
			<td style="text-align:right">'.customFormatMoney($total_damount).'</td>
			<td style="text-align:right">'.customFormatMoney($total_camount).'</td>
			<td style="text-align:right"></td>
			<td style="text-align:center"></td>
			<td style="text-align:left"></td>
			<td style="text-align:center"></td>
			<td style="text-align:left"></td>
			<td style="text-align:left"></td>
		</tr>
		';

	if($opening >= 0){
		$total1_damount = $total1_damount + abs($opening);
	}else{
		$total1_camount = $total1_camount + abs($opening);
	}

	$cb = $total1_damount - $total1_camount;

	if($cb >= 0){
		$cb_flag ='Dr';
	}else{
		$cb_flag ='Cr';
	}
	$table.='
		<tr style="font-weight:bolder">
			<td></td>
			<td></td>
			<td></td>
			<td>Closing Balance</td>
			<td style="text-align:right">'.customFormatMoney($total1_damount).'</td>
			<td style="text-align:right">'.customFormatMoney($total1_camount).'</td>
			<td style="text-align:right">'.customFormatMoney(abs($cb)).'</td>
			<td style="text-align:center">'.$cb_flag.'</td>
			<td style="text-align:left"></td>
			<td style="text-align:center"></td>
			<td style="text-align:left"></td>
			<td style="text-align:left"></td>
		</tr>
		';
	$table .='
	<tr style="font-weight:bolder;display:none">
		<td>
			<input type="text" name="account_code" value="'.$rows['act_code'].'">
			<input type="text" name="account_name" value="'.$rows['ac_name'].'">
			<input type="text" name="transaction_balance_cr" value="'.$total_camount.'">
			<input type="text" name="transaction_balance_dr" value="'.$total_damount.'">
			<input type="text" name="closing_balance_cr" value="'.$total1_camount.'">
			<input type="text" name="closing_balance_dr" value="'.$total1_damount.'">
			<input type="text" name="closing_balance" value="'.abs($cb).'">
			<input type="text" name="closing_balance_flag" value="'.$cb_flag.'">
		</td>
	</tr>
	';
	if($opening >= 0){
		$table .='
		<tr style="font-weight:bolder;display:none">
			<td>
			<input type="text" name="opening_debit" value="'.abs($opening).'">
			<input type="text" name="opening_credit" value="0.00">
			<input type="text" name="opening_balance" value="'.abs($opening).'">
			<input type="text" name="opening_flag" value="Dr">
			</td>
		</tr>';
	}

	if($opening < 0){
		$table .='
		<tr style="font-weight:bolder;display:none">
			<td>
			<input type="text" name="opening_credit" value="'.abs($opening).'">
			<input type="text" name="opening_debit" value="0.00">
			<input type="text" name="opening_balance" value="'.abs($opening).'">
			<input type="text" name="opening_flag" value="Cr">
			
			</td>
		</tr>';
	}

	$data = array('tbody' => $table,'opening' => customFormatMoney(abs($opening)),'closing' => customFormatMoney(abs($closing)),'account_code' => $rows['act_code'],'account_name' => $rows['ac_name'],'cl_flag' => $cl_flag,'op_flag' => $op_flag,'start_date' => $start_date,'end_date' => $end_date);
	echo json_encode($data);
	die();
}else{
	$master_account = combine(getAllRecords('act_master'), 'act_code', 'ac_name');
	$table          = '';

	$select = mysqli_query($conn, "SELECT * FROM `act_master` WHERE act_code = '".$account_code."'");
	$rows = mysqli_fetch_assoc($select);

	$opening_dr_amount = $rows['dr_open_bal'];
	$opening_cr_amount = $rows['cr_open_bal'];

	$debit_op = mysqli_query($conn, "SELECT abs(sum(amount)) AS debit_amount_op FROM `general_ledger` WHERE `debit_account` = '".$account_code."' AND `voucher_date` < '".risformatDate($start_date,'Y-m-d')."'");
	$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` = '".$account_code."' AND `voucher_date` < '".risformatDate($start_date,'Y-m-d')."'");
	$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` = '".$account_code."' AND `voucher_date` >= '".risformatDate($start_date,'Y-m-d')."' AND `voucher_date` <= '".risformatDate($end_date,'Y-m-d')."'");
	$debit_row = mysqli_fetch_assoc($debit);

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

	$opening = ($opening_dr_amount + $debit_op_row['debit_amount_op']) - ($opening_cr_amount + $credit_op_row['credit_amount_op']);

	$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($opening >= 0){
		$op_flag      	= 'Dr';
	}else{
		$op_flag      	= 'Cr';
	}

	if($closing >= 0){
		$cl_flag = 'Dr';
	}else{
		$cl_flag = 'Cr';
	}

	$query = mysqli_query($conn,"SELECT * FROM `general_ledger` WHERE (`credit_account` = '".$account_code."' OR `debit_account` = '".$account_code."') AND `amount` != 0 AND `voucher_date` >= '".risformatDate($start_date,'Y-m-d')."' AND `voucher_date` <= '".risformatDate($end_date,'Y-m-d')."' ORDER BY voucher_date,voucher_no,id");


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

	if($opening >= 0){
	$table.='
	<tr style="font-weight:bolder">
		<td></td>
		<td></td>
		<td></td>
		<td>Opeing</td>
		<td style="text-align:right">'.customFormatMoney(abs($opening)).'</td>
		<td style="text-align:right">0.00</td>
		<td style="text-align:right">'.customFormatMoney(abs($opening)).'</td>
		<td style="text-align:center">Dr</td>
		<td style="text-align:center"></td>
		<td style="text-align:center"></td>
		<td style="text-align:center"></td>
		<td style="text-align:center"></td>
	</tr>
	';
	}

	if($opening < 0){
		$table.='
		<tr style="font-weight:bolder">
			<td></td>
			<td></td>
			<td></td>
			<td>Opeing</td>
			<td style="text-align:right">0.00</td>
			<td style="text-align:right">'.customFormatMoney(abs($opening)).'</td>
			<td style="text-align:right">'.customFormatMoney(abs($opening)).'</td>
			<td style="text-align:center">Cr</td>
			<td style="text-align:center"></td>
			<td style="text-align:center"></td>
			<td style="text-align:center"></td>
			<td style="text-align:center"></td>
		</tr>';
	}

	$tclosing 		 = $opening;
	$total_camount 	 = 0;
	$total_damount 	 = 0;
	$total1_camount  = 0;
	$total1_damount  = 0;
	foreach ($data as $key => $datas) {

		if($datas['flag'] == 'F'){
			$type = 'Credit Note';
		}

		if($datas['flag'] == 'E'){
			$type = 'Debit Note';
		}

		if($datas['flag'] == 'G' && $datas['flag'] == 'S'){
			$type = 'Sales';
		}

		if($datas['flag'] == 'G' && $datas['flag'] == 'P'){
			$type = 'Purchase';
		}

		if($datas['flag'] == 'P'){
			$type = 'Purchase';
		}

		if($datas['flag'] == 'S'){
			$type = 'Sales';
		}

		if($datas['flag'] == 'J'){
			$type = 'Journal Voucher';
		}

		if($datas['flag'] == 'B'){
			$code = explode('-', $datas['voucher_no']);
			if($code[0] == 'BP'){
				$type = 'Bank Payment';		
			}else{
				$type = 'Bank Receipt';
			}
		}

		if($datas['flag'] == 'C'){
			$code = explode('-', $datas['voucher_no']);
			if($code[0] == 'CP'){
				$type = 'Cash Payment';		
			}else{
				$type = 'Cash Receipt';
			}
		}

		$camount = 0;
		$damount = 0;
		if($account_code == $datas['credit_account']){
			$account_name = @$master_account[$datas['debit_account']];
			if($datas['flag'] == 'B' || $datas['flag'] == 'C' || $datas['flag'] == 'J'){
				$camount        = $datas['amount'];	
				$total_camount  += $datas['amount'];	
				$total1_camount += $datas['amount'];	
			}else{
				$camount        = $datas['amount'];
				$total_camount  += $datas['amount'];
				$total1_camount += $datas['amount'];
			}
			
		}

		if($account_code == $datas['debit_account']){
			$account_name = @$master_account[$datas['credit_account']];
			if($datas['flag'] == 'B' || $datas['flag'] == 'C' || $datas['flag'] == 'J'){
				$damount       = $datas['amount'];	
				$total_damount += $datas['amount'];	
				$total1_damount += $datas['amount'];	
			}else{
				$damount        = $datas['amount'];
				$total_damount  += $datas['amount'];	
				$total1_damount += $datas['amount'];	
			}
		}

		$tclosing = ($tclosing + $damount) -  $camount;

		if($tclosing >= 0){
			$bflag = 'Dr';
		}else{
			$bflag = 'Cr';
		}
		$table.='
		<tr>
			<td>'.risFormatDate($datas['voucher_date'],'d/m/Y').'</td>
			<td>'.$datas['voucher_no'].'</td>
			<td>'.$type.'</td>
			<td>'.$account_name.'</td>
			<td style="text-align:right">'.customFormatMoney($damount).'</td>
			<td style="text-align:right">'.customFormatMoney($camount).'</td>
			<td style="text-align:right">'.customFormatMoney(abs($tclosing)).'</td>
			<td style="text-align:center">'.$bflag.'</td>
			<td style="text-align:left">'.$datas['remark'].'</td>
			<td style="text-align:center"><input type="checkbox" class="height10"></td>
			<td style="text-align:left"></td>
			<td style="text-align:left">

			<input type="hidden" name="data['.$key.'][voucher_date]" value="'.risFormatDate($datas['voucher_date'],'d/m/Y').'" class="form-control">

			<input type="hidden" name="data['.$key.'][voucher_no]" value="'.$datas['voucher_no'].'" class="form-control">

			<input type="hidden" name="data['.$key.'][type]" value="'.$type.'" class="form-control">

			<input type="hidden" name="data['.$key.'][account_name]" value="'.$account_name.'" class="form-control">

			<input type="hidden" name="data['.$key.'][damount]" value="'.$damount.'" class="form-control">

			<input type="hidden" name="data['.$key.'][camount]" value="'.$camount.'" class="form-control">

			<input type="hidden" name="data['.$key.'][tclosing]" value="'.$tclosing.'" class="form-control">

			<input type="hidden" name="data['.$key.'][bflag]" value="'.$bflag.'" class="form-control">

			<input type="hidden" name="data['.$key.'][remark]" value="'.$datas['remark'].'" class="form-control">
			</td>


		</tr>
		';
	}

	$table.='
		<tr style="font-weight:bolder">
			<td></td>
			<td></td>
			<td></td>
			<td>Transaction Balance</td>
			<td style="text-align:right">'.customFormatMoney($total_damount).'</td>
			<td style="text-align:right">'.customFormatMoney($total_camount).'</td>
			<td style="text-align:right"></td>
			<td style="text-align:center"></td>
			<td style="text-align:left"></td>
			<td style="text-align:center"></td>
			<td style="text-align:left"></td>
			<td style="text-align:left"></td>
		</tr>
		';

	if($opening >= 0){
		$total1_damount = $total1_damount + abs($opening);
	}else{
		$total1_camount = $total1_camount + abs($opening);
	}

	$cb = $total1_damount - $total1_camount;

	if($cb >= 0){
		$cb_flag ='Dr';
	}else{
		$cb_flag ='Cr';
	}
	$table.='
		<tr style="font-weight:bolder">
			<td></td>
			<td></td>
			<td></td>
			<td>Closing Balance</td>
			<td style="text-align:right">'.customFormatMoney($total1_damount).'</td>
			<td style="text-align:right">'.customFormatMoney($total1_camount).'</td>
			<td style="text-align:right">'.customFormatMoney(abs($cb)).'</td>
			<td style="text-align:center">'.$cb_flag.'</td>
			<td style="text-align:left"></td>
			<td style="text-align:center"></td>
			<td style="text-align:left"></td>
			<td style="text-align:left"></td>
		</tr>
		';
	$table .='
	<tr style="font-weight:bolder;display:none">
		<td>
			<input type="hidden" name="account_code" value="'.$rows['act_code'].'">
			<input type="hidden" name="account_name" value="'.$rows['ac_name'].'">
			<input type="hidden" name="transaction_balance_cr" value="'.$total_camount.'">
			<input type="hidden" name="transaction_balance_dr" value="'.$total_damount.'">
			<input type="hidden" name="closing_balance_cr" value="'.$total1_camount.'">
			<input type="hidden" name="closing_balance_dr" value="'.$total1_damount.'">
			<input type="hidden" name="closing_balance" value="'.abs($cb).'">
			<input type="hidden" name="closing_balance_flag" value="'.$cb_flag.'">
		</td>
	</tr>
	';
	if($opening >= 0){
		$table .='
		<tr style="font-weight:bolder;display:none">
			<td>
			<input type="hidden" name="opening_debit" value="'.abs($opening).'">
			<input type="hidden" name="opening_credit" value="0.00">
			<input type="hidden" name="opening_balance" value="'.abs($opening).'">
			<input type="hidden" name="opening_flag" value="Dr">
			</td>
		</tr>';
	}

	if($opening < 0){
		$table .='
		<tr style="font-weight:bolder;display:none">
			<td>
			<input type="hidden" name="opening_credit" value="'.abs($opening).'">
			<input type="hidden" name="opening_debit" value="0.00">
			<input type="hidden" name="opening_balance" value="'.abs($opening).'">
			<input type="hidden" name="opening_flag" value="Cr">
			
			</td>
		</tr>';
	}
	
	$data = array('tbody' => $table,'opening' => customFormatMoney(abs($opening)),'closing' => customFormatMoney(abs($closing)),'account_code' => $rows['act_code'],'account_name' => $rows['ac_name'],'cl_flag' => $cl_flag,'op_flag' => $op_flag,'start_date' => $start_date,'end_date' => $end_date);
	echo json_encode($data);
	die();
}
?>