<?php
include("../config.php");
require_once '../assets/plugins/pdf/tcpdf.php'; 
$pdf = new PDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
$pdf->SetAutoPageBreak(TRUE, 0);
$pdf->SetFont('helvetica', '', 7, '', true);
$sd = date("Y-m-d", strtotime($_GET['p2']));
$ed = date("Y-m-d", strtotime($_GET['p3']));

$act     = "SELECT * FROM `act_master` WHERE act_code = '".$_GET['p1']."'";
$act_res = mysqli_query($conn,$act);
$act_row = mysqli_fetch_array($act_res);

$master_accounts = combine(getAllRecords('act_master'), 'act_code', 'ac_name');

$gl = "SELECT * FROM `general_ledger` WHERE voucher_date BETWEEN '".$sd."' AND '".$ed."' AND (credit_account = '".$_GET['p1']."' OR debit_account = '".$_GET['p1']."') AND amount > 0 AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
$gl_res = mysqli_query($conn,$gl);
$year           = explode('-', $_GET['p2']);

$start_date     = '01-04-' . $year[0];


	$master_company     = combine(getAllRecords('company_mst'), 'id', 'name');
	$output = '';
	$output .= '<html>
		<table style="width: 100%" cellpadding="3">
			<thead>
			<tr>
				<td style="width:100%;font-size:13px;font-weight:bolder">'.@$master_company[$_SESSION['company_id']].'</td>
			</tr>
			<tr>
				<td style="width:100%;font-size:10px;border-top:1px solid black;border-left:1px solid black;border-right:1px solid black">Ledger Account From Date : '.risFormatDate($_GET['p2'],'d/m/Y').' To Date '.risFormatDate($_GET['p3'],'d/m/Y').'</td>
			</tr>
			<tr>
				<td style="width:100%;font-size:9px;border-left:1px solid black;border-right:1px solid black;font-weight:bolder">'.$act_row['act_code'].' - '.$act_row['ac_name'].'</td>
			</tr>
			<tr>
				<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;border-top:1px solid black;font-weight:bolder">Date</td>

				<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;border-top:1px solid black;font-weight:bolder">Voucher</td>

				<td style="width:38%;font-size:7px;border-left:1px solid black;border-right:1px solid black;border-top:1px solid black;font-weight:bolder">Account</td>

				<td style="width:9%;font-size:7px;border-left:1px solid black;border-right:1px solid black;border-top:1px solid black;font-weight:bolder">Type</td>

				<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;border-top:1px solid black;font-weight:bolder">Debit</td>

				<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;border-top:1px solid black;font-weight:bolder">Credit</td>

				<td style="width:13%;font-size:7px;border-left:1px solid black;border-right:1px solid black;border-top:1px solid black;font-weight:bolder">Balance</td>
			</tr>
		</thead>
		<tbody>';
			$dr_amount = "SELECT sum(amount) as dr_amnt FROM `general_ledger` WHERE debit_account = '".$_GET['p1']."' AND voucher_date >= '".$start_date."' AND voucher_date < '".$sd."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
			$dr_amount_res = mysqli_query($conn,$dr_amount);
			$dr_amount_row = mysqli_fetch_assoc($dr_amount_res);
			$dr_amounts    = $dr_amount_row['dr_amnt'];
			$dr_open_bal   = $act_row['dr_open_bal'];
			$total_debit   = ($dr_open_bal + $dr_amounts);

			$cr_amount = "SELECT sum(amount) as cr_amnt FROM `general_ledger` WHERE credit_account = '".$_GET['p1']."' AND voucher_date >= '".$start_date."' AND voucher_date < '".$sd."' AND company_id = '".$_SESSION['company_id']."' AND branch_id = '".$_SESSION['branch_id']."'";
			$cr_amount_res = mysqli_query($conn,$cr_amount);
			$cr_amount_row = mysqli_fetch_assoc($cr_amount_res);
			$cr_amounts    = $cr_amount_row['cr_amnt'];
			$cr_open_bal   = $act_row['cr_open_bal'];
			$total_credit  = ($cr_amounts + $cr_open_bal);
      $bal           = $total_debit - $total_credit;
			if($bal >= 0){
				$opening_debit = ABS($bal);
				$flags = 'DR';
				$color = 'color:green';    
			}else if($bal < 0){
				$opening_credit = ABS($bal);
				$flags = 'CR';
				$color = 'color:red';
			}else{
				$flags = '';
				$color = '';
			}
      $bal = $opening_debit - $opening_credit;
			$output .='
			<tr>
				<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;border-top:1px solid black;font-weight:bolder"></td>

				<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;border-top:1px solid black;font-weight:bolder"></td>

				<td style="width:38%;font-size:7px;border-left:1px solid black;border-right:1px solid black;border-top:1px solid black;font-weight:bolder">OPENING BALANCE</td>

				<td style="width:9%;font-size:7px;border-left:1px solid black;border-right:1px solid black;border-top:1px solid black;font-weight:bolder"></td>

				<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;border-top:1px solid black;font-weight:bolder">'.customFormatMoney($opening_debit).'</td>

				<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;border-top:1px solid black;font-weight:bolder">'.customFormatMoney($opening_credit).'</td>

				<td style="width:13%;font-size:7px;border-left:1px solid black;border-right:1px solid black;border-top:1px solid black;font-weight:bolder'.$color.'">'.customFormatMoney(abs($bal)).''.$flags.'</td>
			</tr>';
      $total_dr = 0;
      $total_cr = 0;
      while ($rows = mysqli_fetch_array($gl_res)) {
      	$cr   = 0;
        $dr   = 0;

        if($rows['debit_account'] == $_GET['p1']){
          $dr        = $rows['amount'];
        }

        if($rows['credit_account'] == $_GET['p1']){
          $cr         = $rows['amount'];
        }

        if($rows['debit_account'] != $_GET['p1']){
          $a = @$master_accounts[$rows['debit_account']];
        }

        if($rows['credit_account'] != $_GET['p1']){
          $a = @$master_accounts[$rows['credit_account']];
        }

        $bal = ($bal + $dr) - $cr;
				if($bal < 0){
				  $flag      = 'CR';
				  $highlight = "color:red";
				}else{
				  $flag      = 'DR';
				  $highlight  = "color:green";
				}
				$total_cr += $cr;
				$total_dr += $dr;

				if($rows['flag'] == 'MR'){
                $mrp = mysqli_query($conn, "SELECT mr.mr_no,mr.mr_date FROM mr_payment_status JOIN mr ON mr.mr_no = mr_payment_status.mr_no WHERE mr_payment_status.voucher_no = '".$rows['voucher_no']."' GROUP BY mr_no");
                $mrp_row= mysqli_fetch_assoc($mrp);
                $remark = 'MR.NO : '.$mrp_row['mr_no'].' DATE '.risFormatDate($mrp_row['mr_date'],'d.m.Y'); 
              }else if($rows['flag'] == 'LHC'){
                $mrp = mysqli_query($conn, "SELECT lhc.lhc_no,lhc.lhc_date FROM lhc_payment_status JOIN lhc ON lhc.lhc_no = lhc_payment_status.lhc_no WHERE lhc_payment_status.voucher_no = '".$rows['voucher_no']."' GROUP BY lhc_no");
                $mrp_row= mysqli_fetch_assoc($mrp);
                $remark = 'LHC.NO : '.$mrp_row['lhc_no'].' DATE '.risFormatDate($mrp_row['lhc_date'],'d.m.Y'); 
              }else if($rows['flag'] == 'BILLING'){
                $mrp = mysqli_query($conn, "SELECT remark FROM billing WHERE invoice_no = '".$rows['voucher_no']."' GROUP BY invoice_no");
                $mrp_row= mysqli_fetch_assoc($mrp);
                $remark = $mrp_row['remark']; 
              }else if($rows['flag'] == 'BANK PAYMENT'){
                 $payment  = mysqli_query($conn, "SELECT `narration` FROM `payment` WHERE `voucher_no` = '".$rows['voucher_no']."' AND `flag` = 'BP'");
                 $payment_row = mysqli_fetch_assoc($payment);
                 $remark = $payment_row['narration']; 
              }else if($rows['flag'] == 'BANK RECEIPT'){
                 $payment  = mysqli_query($conn, "SELECT `narration` FROM `payment` WHERE `voucher_no` = '".$rows['voucher_no']."' AND `flag` = 'BR'");
                 $payment_row = mysqli_fetch_assoc($payment);
                 $remark = $payment_row['narration']; 
              }else if($rows['flag'] == 'CASH RECEIPT'){
                 $payment  = mysqli_query($conn, "SELECT `narration` FROM `payment` WHERE `voucher_no` = '".$rows['voucher_no']."' AND `flag` = 'CR'");
                 $payment_row = mysqli_fetch_assoc($payment);
                 $remark = $payment_row['narration']; 
              }else if($rows['flag'] == 'CASH PAYMENT'){
                 $payment  = mysqli_query($conn, "SELECT `narration` FROM `payment` WHERE `voucher_no` = '".$rows['voucher_no']."' AND `flag` = 'CP'");
                 $payment_row = mysqli_fetch_assoc($payment);
                 $remark = $payment_row['narration']; 
              }else{
                $remark = '';
              }
        
      	$output .='<tr>
       		<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:center">'.risFormatDate($rows['voucher_date'], 'd.m.Y').'</td>

         	<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:center">'.$rows['voucher_no'].'</td>

         	<td style="width:38%;font-size:7px;border-left:1px solid black;border-right:1px solid black;">'.$a.'</td>

         	<td style="width:9%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:center">'.$rows['flag'].'</td>

         	<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:right">'.customFormatMoney($dr).'</td>

         	<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:right">'.customFormatMoney($cr).'</td>

         	<td style="width:13%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:right'.$highlight.'">'.customFormatMoney(abs($bal)).'&nbsp;'.$flag.'</td>
				</tr>';

				if($remark != ''){

      		$output .='<tr>
						<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:center"></td>

						<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:center"></td>

						<td style="width:38%;font-size:6px;border-left:1px solid black;border-right:1px solid black;">'.$remark.'</td>

						<td style="width:9%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:center"></td>

						<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:right"></td>

						<td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:right"></td>

						<td style="width:13%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:right"></td>
					</tr>';
				}
      }


      $output .='<tr>
           <td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:center;border-top:1px solid black;border-bottom:1px solid black"></td>

           <td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:center;border-top:1px solid black;border-bottom:1px solid black"></td>

           <td style="width:38%;font-size:7px;border-left:1px solid black;border-right:1px solid black;border-top:1px solid black;text-align:right;font-weight:bolder;border-bottom:1px solid black">Closing Balance </td>

           <td style="width:9%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:center;border-top:1px solid black;border-bottom:1px solid black"></td>

           <td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:right;border-top:1px solid black;font-weight:bolder;border-bottom:1px solid black">'.customFormatMoney($opening_debit + $total_dr).'</td>

           <td style="width:10%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:right;border-top:1px solid black;font-weight:bolder;border-bottom:1px solid black">'.customFormatMoney($opening_credit + $total_cr).'</td>

           <td style="width:13%;font-size:7px;border-left:1px solid black;border-right:1px solid black;text-align:right;border-top:1px solid black;border-bottom:1px solid black;font-weight:bolder"></td>
			</tr>';
      
      $output .='
			</tbody>
			</table>
			</html>';

$pdf->AddPage();
$pdf->writeHTML($output, true, false, false, false, '');
$pdf->SetPrintFooter(false);
$pdf->SetTitle('ACCOUNT LEDGER'.' - '.$act_code.'('.$act_name.')');
$pdf->Output('account_ledger'.'_'.$act_code.'('.$act_name.').pdf', 'I');
?>